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 menu on mouse right click "Workbook Navigation showing you the list of all open workbooks and worksheets in ea... -
Macro to Export Range in Json Format Option Explicit Sub export_in_json_format() Dim fs As Object Dim jsonfile Dim rangetoex...
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)