copy and stack all tabs data to one sheet in another workbook using vba

NateD1

New Member
Joined
Apr 1, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Hi All,
i have the below code i found which copys all data in multiple tabs in a workbook to one sheet.
would some one be able to adjust this code or simplifying so that it only copies particular tabs instead of all tabs. (i.e only Sheet1,Sheet3,Sheet4,Sheet5)
i need all this data then exported into another work book.

example of what ideally id like it to do, Main workbook where data to be copied to: workbook1, data where is stored and macro combines sheets: Workbook2
workbook 1, run macro > opens workbook 2 > combines all data on named tabs > exports combined stacked data to "Sheet1" on workbook1 > close Workbook2

VBA Code:
Sub Combine()
    Dim J As Integer
    Dim s As Worksheet

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    For Each s In ActiveWorkbook.Sheets
        If s.Name <> "Combined" Then
            Application.GoTo Sheets(s.Name).[a1]
            Selection.CurrentRegion.Select
            ' Don't copy the headings
            Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
            Selection.Copy Destination:=Sheets("Combined"). _
              Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
End Sub

thanks
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,007
Rich (BB code):
Sub Copy_Sheets_To_Master()
    Application.ScreenUpdating = False
    Dim wkbSource As Workbook, wsDest As Worksheet, ws As Worksheet, lRow As Long
    Set wsDest = ThisWorkbook.Sheets("Master")
    Set wkbSource = Workbooks.Open("Z:\Filelocation\" & Range("A1"))
    For Each ws In Sheets(Array("Sheet1", "Sheet3", "Sheet4", "Sheet5"))
        With ws
            lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Cells(2, 1).Resize(lRow - 1, 7).Copy wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1)
        End With
    Next ws
    wkbSource.Close False
    Application.ScreenUpdating = True
End Sub
Change the range (in red) to the cell that contains the file name. Make sure that the file name in that cell includes the extension, in your case xlsb. Also, when replying, please click the "Reply" button, not the "Reply With Quote" button. This helps to avoid clutter.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

NateD1

New Member
Joined
Apr 1, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Thank you! appreciate all your help with this!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,255
Messages
5,576,981
Members
412,757
Latest member
Thalalala
Top