copy one column of data after another

[Don Doan]

Hi there,
Perhaps someone can help me.
I have 2 separate spreadsheets where in both sheets, column B has some data
in it and the third spreadsheet is blank. How can I copy all data from column
B from the first spreadsheet onto column A of the third spreadsheet? and then
copy all data from column B from the second spreadsheet and also put it
immediately right after the next blank space in column A of the third
spreadsheet. So basically, I'm combining data from column B from the first 2
spreadsheets into one column in the third spreadsheet.

thanks so much for your help.

 

[Rick Rothstein]

A couple of questions for clarification. Does either or both of your Column
B's have header rows? If so, what row does the data start on and will there
be a header row on Sheet 3? Is your data solid in each Column B or can there
be blank cells scattered in and among your data?

 

[Don Doan]

Both columns B have header row so the data begin at cell B2.
But for sheet 3, no header row needed...so data can start at cell A1.

there are no blank cells in both column B....

 

[Rick Rothstein]

Give this code a try...

Sub CombineColBs()
Dim LastRow1 As Long
Dim LastRow2 As Long
Const StartRow As Long = 2
With Worksheets("Sheet1")
LastRow1 = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("B2:B" & LastRow1).Copy Worksheets("Sheet3").Range("A1")
End With
With Worksheets("Sheet2")
LastRow2 = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("B2:B" & LastRow1).Copy Worksheets("Sheet3"). _
Range("A1").Offset(LastRow1 - 1)
End With
End Sub

 

[Don Doan]

thank you so much, it works.....!!!!!

Give this code a try...

Sub CombineColBs()
Dim LastRow1 As Long
Dim LastRow2 As Long
Const StartRow As Long = 2
With Worksheets("Sheet1")
LastRow1 = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("B2:B" & LastRow1).Copy Worksheets("Sheet3").Range("A1")
End With
With Worksheets("Sheet2")
LastRow2 = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("B2:B" & LastRow1).Copy Worksheets("Sheet3"). _
Range("A1").Offset(LastRow1 - 1)
End With
End Sub

 

[Don Doan]

I'm trying to play with the code here...perhaps you can correct this...
Assuming now I have the third spreadsheet where data begins at cell
D3....I'm trying to add all data from the first 3 sheets into sheet 4....here
is the code that I'm playing with...but it doesn't seemed to work

Sub Macro3()
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim LastRow3 As Long
Const StartRow As Long = 2
With Worksheets("Sheet1")
LastRow1 = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("C2:C" & LastRow1).Copy Worksheets("Sheet4").Range("A1")
End With

With Worksheets("Sheet2")
LastRow2 = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("C2:C" & LastRow2).Copy
Worksheets("Sheet4").Range("A1").Offset(LastRow1 - 1)
End With

With Worksheets("Sheet3")
LastRow3 = .Cells(.Rows.Count, "D").End(xlUp).Row
.Range("D3" & LastRow3).Copy
Worksheets("Sheet4").Range("A1").Offset(LastRow2 + 2)
End With
End Sub

 

[Rick Rothstein]

Try this (notice I added some Const statements where you can assign changes
and which should make it easier for you to figure out how to add additional
sheets later on)...

Sub CombineColBs()
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim LastRow3 As Long

Const StartRow As Long = 2
Const FirstCopySource As String = "Sheet1"
Const SecondCopySource As String = "Sheet2"
Const ThirdCopySource As String = "Sheet3"
Const DestinationSheet As String = "Sheet4"
Const FirstAddrStub As String = "C2:C"
Const SecondAddrStub As String = "C2:C"
Const ThirdAddrStub As String = "D2"
Const DestinationSheetDataColumn As String = "A"

With Worksheets(FirstCopySource)
LastRow1 = .Cells(.Rows.Count, Left(FirstAddrStub, 1)).End(xlUp).Row
.Range(FirstAddrStub & LastRow1).Copy Worksheets( _
DestinationSheet).Range("A1")
End With
With Worksheets(SecondCopySource)
LastRow2 = .Cells(.Rows.Count, Left(SecondAddrStub, 1)).End(xlUp).Row
.Range(SecondAddrStub & LastRow2).Copy Worksheets( _
DestinationSheet).Range("A1").Offset(LastRow1 - 1)
End With
With Worksheets(ThirdCopySource)
LastRow3 = .Cells(.Rows.Count, Left(ThirdAddrStub, 1)).End(xlUp).Row
.Range(ThirdAddrStub & LastRow3).Copy Worksheets( _
DestinationSheet).Range("A1"). _
Offset(LastRow1 + LastRow2 - 2)
End With
End Sub

 

[Don Doan]

work beautifully....thanks.

Try this (notice I added some Const statements where you can assign changes
and which should make it easier for you to figure out how to add additional
sheets later on)...

Sub CombineColBs()
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim LastRow3 As Long

Const StartRow As Long = 2
Const FirstCopySource As String = "Sheet1"
Const SecondCopySource As String = "Sheet2"
Const ThirdCopySource As String = "Sheet3"
Const DestinationSheet As String = "Sheet4"
Const FirstAddrStub As String = "C2:C"
Const SecondAddrStub As String = "C2:C"
Const ThirdAddrStub As String = "D2"
Const DestinationSheetDataColumn As String = "A"

With Worksheets(FirstCopySource)
LastRow1 = .Cells(.Rows.Count, Left(FirstAddrStub, 1)).End(xlUp).Row
.Range(FirstAddrStub & LastRow1).Copy Worksheets( _
DestinationSheet).Range("A1")
End With
With Worksheets(SecondCopySource)
LastRow2 = .Cells(.Rows.Count, Left(SecondAddrStub, 1)).End(xlUp).Row
.Range(SecondAddrStub & LastRow2).Copy Worksheets( _
DestinationSheet).Range("A1").Offset(LastRow1 - 1)
End With
With Worksheets(ThirdCopySource)
LastRow3 = .Cells(.Rows.Count, Left(ThirdAddrStub, 1)).End(xlUp).Row
.Range(ThirdAddrStub & LastRow3).Copy Worksheets( _
DestinationSheet).Range("A1"). _
Offset(LastRow1 + LastRow2 - 2)
End With
End Sub