If you want to merge data from all sheets to single. For example we have muliple month wise sheets having sales details and we need to make one consolidated sheet by copying all data from each sheets and adding them to one.
Download Working File
Here is the code-
Option Explicit
Sub merge1()
Dim i As Long, z As Long
' add new sheet at last and name it merge
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Merge"
For i = 1 To Sheets.Count - 1
' add headers from first sheet
If i = 1 Then
' find last filled row in the sheet
z = Sheets(i).Range("a1048576").End(xlUp).Row
' paste the data on the merge sheet
Sheets(i).Rows("1:" & z).Copy Destination:=Sheets("Merge").Range("a1")
Else
' find last filled row in the sheet
z = Sheets(i).Range("a1048576").End(xlUp).Row
' paste the data on the merge sheet
Sheets(i).Rows("2:" & z).Copy Destination:=Sheets("Merge").Range("a" & Sheets("Merge").Range("a1048576").End(xlUp).Row + 1)
End If
Next
End Sub
Subscribe to:
Post Comments (Atom)
-
Please make sure that you have the latest Version Tool Last Updated on 17 Feb, 2012 Version 1.6 English Version For Excel 2007 &... -
Adding Marquee in Excel worksheet Step 1 Step 2 Example 1 Continuous scrolling Add below code module1 or any new module Sub r...
Hi,
ReplyDeleteThis code does almost all I was looking for. I am very new to VBA and I am having some difficulties to find a way to add the name of the imported Sheet at the end of each and every rows, i.e. in a new column labelled "Source".
I have tried the following and not to avail.
Sheets("Merge").ActiveCells.Offset(0, 1).Value = Sheets(i).Name
Thank you in advance for your suggestion.
Best,
Donat
' chnage g as per ur column no
ReplyDeleteSheets("Merge").Range("g" & Sheets("Merge").Range("a1048576").End(xlUp).Row + 1).Value = Sheets(i).Name
add this before
Sheets(i).Rows("2:" & z).Copy Destination:=Sheets("Merge").Range("a" & Sheets("Merge").Range("a1048576").End(xlUp).Row + 1)