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)
-
If you want to add a new pop up button on mouse right click menu and as soon as you click on it . It shows you multiple buttons with macro a... -
Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...
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)