Copy multiple tabs and past as link into single tab

[snai7247]

Hi,

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

I have a excel file that contains multiple tabs with the same header but The # of rows can vary).

What i want to do is to merge all the data from each tab together into one tab. When copy and paste, paste as link so if a user changes the data in first tab it will reflect in "stacked master tab". For example, copy all data in first tab (include header) into a new tab, then copy the second,third...etc(without header) all as link.

 

[Domenic]

Maybe something like this...

Option Explicit

Const MASTER_SHEET_NAME As String = "Stacked Master"

Sub CombineWorksheets()

    Application.ScreenUpdating = False

    'delete worksheet called "Stacked Master", if one already exists
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(MASTER_SHEET_NAME).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    'add new worksheet for the combined data
    Dim destinationWorksheet As Worksheet
    Set destinationWorksheet = ThisWorkbook.Worksheets.Add(before:=ThisWorkbook.Worksheets(1))
    destinationWorksheet.Name = MASTER_SHEET_NAME
    
    'combine the data from worksheets
    Dim copyRange As Range
    Dim nextRow As Long
    Dim sheetIndex As Long
    nextRow = 1
    For sheetIndex = 2 To ThisWorkbook.Worksheets.Count
        If sheetIndex > 2 Then
            With ThisWorkbook.Worksheets(sheetIndex).UsedRange
                Set copyRange = .Offset(1, 0).Resize(.Rows.Count - 1)
            End With
        Else
            Set copyRange = ThisWorkbook.Worksheets(sheetIndex).UsedRange
        End If
        copyRange.Copy
        With destinationWorksheet
            .Activate
            .Cells(nextRow, "a").Select
            .Paste Link:=True
        End With
        nextRow = nextRow + copyRange.Rows.Count
    Next sheetIndex
    
    destinationWorksheet.Range("a1").Select
    
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True
    
    MsgBox "The data has been combined in the 'Stacked Master' worksheet.", vbInformation
    
End Sub

Hope this helps!

 

[snai7247]

Maybe something like this...

Option Explicit

Const MASTER_SHEET_NAME As String = "Stacked Master"

Sub CombineWorksheets()

    Application.ScreenUpdating = False

    'delete worksheet called "Stacked Master", if one already exists
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(MASTER_SHEET_NAME).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    'add new worksheet for the combined data
    Dim destinationWorksheet As Worksheet
    Set destinationWorksheet = ThisWorkbook.Worksheets.Add(before:=ThisWorkbook.Worksheets(1))
    destinationWorksheet.Name = MASTER_SHEET_NAME
   
    'combine the data from worksheets
    Dim copyRange As Range
    Dim nextRow As Long
    Dim sheetIndex As Long
    nextRow = 1
    For sheetIndex = 2 To ThisWorkbook.Worksheets.Count
        If sheetIndex > 2 Then
            With ThisWorkbook.Worksheets(sheetIndex).UsedRange
                Set copyRange = .Offset(1, 0).Resize(.Rows.Count - 1)
            End With
        Else
            Set copyRange = ThisWorkbook.Worksheets(sheetIndex).UsedRange
        End If
        copyRange.Copy
        With destinationWorksheet
            .Activate
            .Cells(nextRow, "a").Select
            .Paste Link:=True
        End With
        nextRow = nextRow + copyRange.Rows.Count
    Next sheetIndex
   
    destinationWorksheet.Range("a1").Select
   
    Application.CutCopyMode = False
   
    Application.ScreenUpdating = True
   
    MsgBox "The data has been combined in the 'Stacked Master' worksheet.", vbInformation
   
End Sub

Hope this helps!

Thanks that was really helpful

 

[Domenic]

You're very welcome, glad I could help.

Cheers!

 

[jordsh7]

Maybe something like this...

Option Explicit

Const MASTER_SHEET_NAME As String = "Stacked Master"

Sub CombineWorksheets()

    Application.ScreenUpdating = False

    'delete worksheet called "Stacked Master", if one already exists
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(MASTER_SHEET_NAME).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    'add new worksheet for the combined data
    Dim destinationWorksheet As Worksheet
    Set destinationWorksheet = ThisWorkbook.Worksheets.Add(before:=ThisWorkbook.Worksheets(1))
    destinationWorksheet.Name = MASTER_SHEET_NAME
   
    'combine the data from worksheets
    Dim copyRange As Range
    Dim nextRow As Long
    Dim sheetIndex As Long
    nextRow = 1
    For sheetIndex = 2 To ThisWorkbook.Worksheets.Count
        If sheetIndex > 2 Then
            With ThisWorkbook.Worksheets(sheetIndex).UsedRange
                Set copyRange = .Offset(1, 0).Resize(.Rows.Count - 1)
            End With
        Else
            Set copyRange = ThisWorkbook.Worksheets(sheetIndex).UsedRange
        End If
        copyRange.Copy
        With destinationWorksheet
            .Activate
            .Cells(nextRow, "a").Select
            .Paste Link:=True
        End With
        nextRow = nextRow + copyRange.Rows.Count
    Next sheetIndex
   
    destinationWorksheet.Range("a1").Select
   
    Application.CutCopyMode = False
   
    Application.ScreenUpdating = True
   
    MsgBox "The data has been combined in the 'Stacked Master' worksheet.", vbInformation
   
End Sub

Hope this helps!

Hi, I also am looking for something like this. I have tried this and it partially worked however it only looked at the first 4 of my tabs. Do you know how i can get it to look at 500+?

Thanks

 

[Domenic]

Hi,

Actually, as it stands, the code should automatically loop through every worksheet in the workbook running the code, and copy the data from each one of those worksheets to the master worksheet.

Cheers!

 

[jordsh7]

Hi,

Actually, as it stands, the code should automatically loop through every worksheet in the workbook running the code, and copy the data from each one of those worksheets to the master worksheet.

Cheers!

I think its working on now! It must be my data! Cheers