CODEWITHSUNDEEP
excelvba
Saagar
Saagar

Reputation: 15

Select range and copy

I am trying to copy data from one file to another. The range changes everytime I download the source file. I created a macro to copy from one workbook to another, but is not working. 

Sub Copy_Method()

Dim lRow As Long
Dim lCol As Long

lRow = Workbooks("Active Dealers with State.xlsx").Worksheets("Active Dealers With State").Cells(Rows.Count, 1).End(xlUp).Row
lCol = Workbooks("Active Dealers with State.xlsx").Worksheets("Active Dealers With State").Cells(1, Columns.Count).End(xlToLeft).Column

Workbooks("Active Dealers with State.xlsx").Worksheets("Active Dealers With State").Range("A4", Cells(lRow, lCol).Select).Copy _
    Workbooks("Working Sheet.xlsx").Worksheets("Active Dealer with State").Range("A4")

End Sub

What is the issue here? I want to copy the entire data from Active Dealers with State workbook to the Working sheet file.

Upvotes: 1

Views: 145

Answers (2)

Tim Williams
Tim Williams

Reputation: 166825

Some more variables and being more explicit about your range/cells references should improve things:

Sub Copy_Method()

    Dim lRow As Long
    Dim lCol As Long
    Dim wsSource as worksheet, wsDest as worksheet

    Set wsSource = Workbooks("Active Dealers with State.xlsx").Worksheets("Active Dealers With State")
    Set wsDest = Workbooks("Working Sheet.xlsx").Worksheets("Active Dealer with State")


    lRow = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
    lCol = wsSource.Cells(1, Columns.Count).End(xlToLeft).Column

    'note qualified Cells reference below...
    With wsSource
        .Range(.Range("A4"), .Cells(lRow, lCol)).Copy wsDest.Range("A4")
    End With

End Sub

Upvotes: 1

Hasib_Ibradzic
Hasib_Ibradzic

Reputation: 666

I don't think you should be using the copy/paste as that is very inefficient. You could just set the values equal to each other

Sub Copy_Method()
    Dim lRow As Long, lCol As Long
    Dim orgWS As Worksheet, newWS As Worksheet

    Set orgWS = Workbooks("Active Dealers with State.xlsx").Worksheets("Active Dealers With State")
    Set newWS = Workbooks("Working Sheet.xlsx").Worksheets("Active Dealer with State")

    lRow = orgWS.Cells(Rows.Count, 1).End(xlUp).Row
    lCol = orgWS.Cells(1, Columns.Count).End(xlToLeft).Column

    newWS.Range(newWS.Cells(4, 1), newWS.Cells(lRow, lCol)) = orgWS.Range(orgWS.Cells(4, 1), orgWS.Cells(lRow, lCol)).Value
End Sub

Upvotes: 0

Related Questions