Select range and copy

Question

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.

Possible duplicate of [Copy from one workbook and paste into another](https://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another) — Samuel Hulla, Mar 14 '19 at 21:32

Tim Williams · Answer 1 · 2019-03-15T14:47:57.543

1

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

edited Mar 15 '19 at 14:47

answered Mar 14 '19 at 20:26

Tim Williams

154,628
8
97
125

On which line ? – Tim Williams Mar 14 '19 at 21:30
See my edits above - missed deleting an extra `Select` – Tim Williams Mar 15 '19 at 14:49
Still just the first column, i.e. column A is only being copied – Saagar Mar 19 '19 at 21:12
What's the value of `lCol`? Add `Debug.Print lRow, lCol` and see what is output (in the Immediate pane in the VB editor) In your question you indicate the headers are in the first row: if that's not the case then you'll need to make changes... E.g. `lCol = orgWS.Cells(3, Columns.Count).End(xlToLeft).Column` – Tim Williams Mar 19 '19 at 21:16

score 0 · Answer 2 · answered Mar 14 '19 at 20:40

0

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

answered Mar 14 '19 at 20:40

Hasib_Ibradzic

666
5
23

Just the first column is being copied, there are two columns with the values in it – Saagar Mar 14 '19 at 21:19
are the values in the first row? The code pulls through every row and column for me. lCol looks at the last column from row 1. If it's in a different row then you would just need to change the 1 to whatever column you want. – Hasib_Ibradzic Mar 14 '19 at 21:27
*> "using copy paste is very inefficient comapred to setting values"* - [Not necessarily always true](https://stackoverflow.com/questions/51826865/what-is-the-correct-way-to-copy-paste-data-in-vba) – Samuel Hulla Mar 14 '19 at 21:28
if the row number is dynamic and you can use this `lCol = orgWS.UsedRange.Columns(orgWS.UsedRange.Columns.Count).Column` – Hasib_Ibradzic Mar 14 '19 at 21:28
the content starts from A3, however the third row is the heading part. Therefore, the data starts from A4 and I have multiple files from where data needs to be copied. One file has 4 columns, one has 10 and the other one has 2 columns. This code I tried on the one with 2 columns, but only the first column data (VERTICAL) is being copied. – Saagar Mar 15 '19 at 02:03

Select range and copy

2 Answers2