Log In
Register
Home
August 25, 2016
Offline
Hi
im having trouble Merging separate columns of data in different sheets in to another sheet.
e.g. Column A in Sheet 1 and 2 has data, I want to combine the 2 columns of data in sheet 3.
In sheet 3, the data from sheet 2 needs to start from the end of the data from sheet 1.
the data in sheet 1 and 2 is not fixed, so the number of populated cells could change.
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi
Give this a try
Sub MergeData()
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim LastRow3 As Long
LastRow1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LastRow2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Range("A1:A" & LastRow1).Copy Sheets("Sheet3").Range("A1")
LastRow3 = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Sheet2").Range("A1:A" & LastRow2).Copy Sheets("Sheet3").Range("A" & LastRow3)
End Sub
Sunny
Answers Post
August 25, 2016
Offline
That's Brilliant Sunny. I really appreciate your help. Thanks.
if have time would you mind explaining what the code means, so I can understand how you did it?
Cheers
Ahmed.
VIP
Trusted Members
June 25, 2016
Offline
Hi Ahmed
See my explanation in red below.
Find the last row number of the sheets with data as I need to copy from Column A Row 1 till the last row containing data.
LastRow1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row This will give me the last row number
LastRow2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row This will give me the last row number
Copy data from Sheet1, starting from Column A Row 1 till the last row with data to Sheet3. You can do this without needing to use the Copy and Paste command (like below)
Sheets("Sheet1").Range("A1:A" & LastRow1).Copy Sheets("Sheet3").Range("A1")
Find out what is the next blank row (i.e. Last row number + 1 ) in Sheet3 after pasting data from Sheet1. I need it to append (add) data to the next blank row.
LastRow3 = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row + 1
Copy data from Sheet2 to next blank row in Sheet3
Sheets("Sheet2").Range("A1:A" & LastRow2).Copy Sheets("Sheet3").Range("A" & LastRow3)
Hope my explanation is clear.
Suny
December 5, 2016
Offline
Thanks Sunny [ Suny] I think you do give a very clear explanation of the VB code to everyone.
Now even layman can get an insight and understanding to know what these codes is getting at and accomplish for users.
1 Guest(s)
