VBA Copy column data ranges to another tab

[robocop1906]

I’m trying to develop code to copy data from multiple column ranges i.e. A2:A with “A” being dynamic based on how far down it’ populated. Below is what I’ve come up with so far. I’m trying to get the first column copy to work but can’t seem to.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I also need a follow-up macro to find the first blank cell in the destination sheet and copy the column range from another tab source.<o></o>
<o> </o>
So the manual way would be to go to the first source tab and copy the columns rows 2 down the last data entry then paste it to the destination tab, then go to the second source tab and repeat but pasting the data at the bottom of the destination tab.<o></o>

 

[VoG]

Try like this: sheets 2 to N -> Sheet1

Sub test()
Dim LR As Long, i As Long
For i = 2 To Worksheets.Count
    With Sheets(i)
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A2:A" & LR).Copy Destination:=Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With
Next i
End Sub

 

[robocop1906]

Sorry I'm really green to VBA. My source sheet is 'RawOpexData' and my destination is 'ConsolidatedData' so where would these be defined in your code?

 

[VoG]

You mentioned multiple sheets. If there are just two

Sub test()
Dim LR As Long
With Sheets("RawOpexData")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A2:A" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub

 

[robocop1906]

Thanks, that works but I have 3 tabs involved. To source and one destination. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I tried using two separate Subs and I get weird results because not all columns outside of column A have data in them. The strangest thing is when I run the second sub it pulls data from other sheets with in my work book that I'm not calling, it does it for only column A though.
<o> </o>
See what I have below.

Sub ConsolidateData1()
' This macro consolidates the data from the RawOpexData tab to the 'ConsolidatedData'
Dim LR As Long
With Sheets("RawOpexData")
LR = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:A" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("A" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("B" & Rows.Count).End(xlUp).Row
.Range("B2:B" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("B" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("c" & Rows.Count).End(xlUp).Row
.Range("c2:c" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("c" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("d" & Rows.Count).End(xlUp).Row
.Range("d2:d" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("d" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("e" & Rows.Count).End(xlUp).Row
.Range("e2:e" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("e" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("f" & Rows.Count).End(xlUp).Row
.Range("f2:f" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("f" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("g" & Rows.Count).End(xlUp).Row
.Range("g2:g" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("g" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("h" & Rows.Count).End(xlUp).Row
.Range("h2:h" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("h" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("i" & Rows.Count).End(xlUp).Row
.Range("i2:i" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("i" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("j" & Rows.Count).End(xlUp).Row
.Range("j2:j" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("j" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub

Sub ConsolidateData2()
' This macro consolidates the data from the RawStampData tab to the 'ConsolidatedData'
Dim LR As Long
With Sheets("RawStampData")
LR = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:A" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("A" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("a" & Rows.Count).End(xlUp).Row
.Range("B2:B" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("B" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("a" & Rows.Count).End(xlUp).Row
.Range("c2:c" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("c" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("a" & Rows.Count).End(xlUp).Row
.Range("e2:e" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("d" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("a" & Rows.Count).End(xlUp).Row
.Range("j2:j" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("e" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("a" & Rows.Count).End(xlUp).Row
.Range("k2:k" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("f" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("a" & Rows.Count).End(xlUp).Row
.Range("l2:l" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("g" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("a" & Rows.Count).End(xlUp).Row
.Range("p2" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("h" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("a" & Rows.Count).End(xlUp).Row
.Range("s2:s" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("i" & Rows.Count).End(xlUp).Offset(1)

LR = .Range("a" & Rows.Count).End(xlUp).Row
.Range("v2:v" & LR).Copy Destination:=Sheets("ConsolidatedData").Range("j" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub<o></o>

 

[robocop1906]

What if there are multiples