Pages

Saturday, May 12, 2012

Merge Series ----- Merge data from multiple sheets to one

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

2 comments:

  1. Hi,

    This 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

    ReplyDelete
  2. ' chnage g as per ur column no

    Sheets("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)

    ReplyDelete