Register
Log In
Unanswered
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
Dashboards
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.
Dashboards
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.
Most Users Ever Online: 57
Currently Online:
16 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
sugi gunawan
janja mesec
John DiMartino
lizette acosta
eric johnson
Kim Bond
Nichelle Stevens
Manuel Rodriguez
peter wang
Amit Kanekar
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
