VBA - Merge all tabs with same header into a new tab

[mabalo76]

Hi,

Thanks in advance since i am pretty new to Excel VBA.

I have a excel file and contains multiple tabs with the same header. Each tab contains some data(The # of rows can vary). The data in each tab contains some formula.

What i want to do is to merge all the data from each tab together. When copy and paste, only paste the Value of the data. For example, copy all data in first tab (include header) into a new tab, then copy the second, third, ... tab data from the row 2 to the end of row(don't include header from tab2) and paste in the new tab at the end. Please note: only paste the value of the data, don't paste the functions.

Thanks,
Dan

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "Summary"
    Sheets(1).Rows(1).Copy Cells(1, 1)
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            ws.UsedRange.Offset(1, 0).Copy
            Sheets("Summary").Cells(Sheets("Summary").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[mabalo76]

Works perfect. Thank you very much

 

[mumps]

You are very welcome.

 

[DanteAmor]

Try this:

Sub Merge_all_tabs()
  Set sh = Worksheets.Add(After:=Sheets(Sheets.Count))
  sh.Name = "New tab"
  Sheets(1).Rows(1).Copy sh.Rows(1)
  For i = 1 To Sheets.Count - 1
      u = sh.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count)(2).Row
      sh.Range("A" & u).Resize(Sheets(i).UsedRange.Rows.Count, Sheets(i).UsedRange.Columns.Count).Value = Sheets(i).UsedRange.Offset(1).Value
  Next
End Sub

 

[alansidman]

An alternative solution would be to load each tab into Power Query and then Append each tab. A quick and easy step that does not require any coding.

 

[snai7247]

Hi there,
Is there a way to do this but instead of pasting values, paste as a link so that any changes made in the source sheet will reflect in the target sheets?

 

[alansidman]

In Power Query, any changes in the source documents are automatically updated in the output.