0

Aim

Copy column from one worksheet to another.

Issue & Example

I want to copy a column from S1 to S2 but I want the pasting to start on the second row. I can make it copy to the first cell/row and down but not the second. The code example works for the first four lines, however when I change B1 to B2 it fails to I've also tried adding a Range but to no avail.

Code:

Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("BatchData")
    Set s2 = Sheets("Portal_Aligned")
    s1.Range("A:A" & LastRow).Copy s2.Range("B1")
    s1.Range("A:A" & LastRow).Copy s2.Range("B2:B" & LastRow)

Ref:

How to select a range of the second row to the last row

Community
  • 1
  • 1
indofraiser
  • 1,014
  • 3
  • 18
  • 50

1 Answers1

1

By additing the row number to "A1:A" & lastRow when you copy and by incrementing the LastRow by one you ensure that the ranges are of the same size.

Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Worksheets("BatchData")
    Set s2 = Worksheets("Portal_Aligned")
    s1.Range("A1:A" & LastRow).Copy s2.Range("B1")
    s1.Range("A1:A" & LastRow).Copy s2.Range("B2:B" & (LastRow+1))
Genie
  • 355
  • 1
  • 13
  • Hi @Genie If I put A1 this does not work. When I change it to A:A it copies. However regardless of this the s2.Range("B2:... still puts it into the first row. I tried changing this to B5 but it still copied to the first row. – indofraiser Jan 21 '16 at 14:07
  • Check updated code. I did not realise earlier that you did not set your sheets correctly. – Genie Jan 21 '16 at 14:12