Copy a range of data and paste it into a separate sheet cell

Question

For some reason, the range only copies over the first two rows starting in A1 on sheet "11937" and I need it to start on cell A2 and copy all data up until column AL and paste that data in sheet "STS" starting at C2.

Please help

With Sheets("11937")
.Range("A2:AL" & Cells(Rows.Count, "B").End(xlUp).Row).CopyDestination:=Sheets("STS").Range("C2")
End With

score 1 · Answer 1 · answered Aug 16 '16 at 17:15

1

Either your Col B is empty or Cells(Rows.Count, "B") is referring to the Activesheet which may not be 11937. Hence it is always advisable to fully qualify your objects.

Here is an example

Dim lRow As Long

With Sheets("11937")
    lRow = .Range("B" & .Rows.Count).End(xlUp).Row

    .Range("A2:AL" & lRow).Copy Destination:=Sheets("STS").Range("C2")
End With

answered Aug 16 '16 at 17:15

Siddharth Rout

147,039
17
206
250

Even with that it still only pastes the two rows including one being the headers, which I do not want... – JHags Aug 16 '16 at 17:30
Is you col B empty? Try changing `.Range("B" & .Rows.Count).End(xlUp).Row` to `.Range("A" & .Rows.Count).End(xlUp).Row` – Siddharth Rout Aug 16 '16 at 17:31
All the columns have data – JHags Aug 16 '16 at 17:34
This is strange. Can you step through the code and see what is the value of `LRow` – Siddharth Rout Aug 16 '16 at 17:35
Nope that is not necessary – Siddharth Rout Aug 16 '16 at 17:36
I thought that (Rows.Count, "B") refers to the last cell in column B that has data – JHags Aug 16 '16 at 17:36
Yes it does but since it is not fully qualified, it will refer to the active sheet.. which may or may not be `11937`. See [THIS](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) on how to find last row. – Siddharth Rout Aug 16 '16 at 17:37
The activesheet is "11937", I still do not understand as why it is not working,...what other information can I post? – JHags Aug 16 '16 at 17:47
Can you step through the code and check what is the value of `Lrow`? – Siddharth Rout Aug 16 '16 at 17:53

Copy a range of data and paste it into a separate sheet cell

1 Answers1