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

[NateD1]

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

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]

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.

 

[NateD1]

Thank you! appreciate all your help with this!

 

[mumps]

You are very welcome.

 

[chrishlee1228]

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

Regarding this code is there a way to copy all sheets based on tab names listed in another tab of the macro workbook? For example the list in a tab labeled "tabs" contains a list of tabs that I want copy and pasted: "Sheet1", "Sheet2", etc. instead of having to update it in the VBA screen every time there is a new updated tab?

Also I want a conditional copy/paste where if in my column labeled "Already Pasted" for each line of data with a string text has a "Yes" then we don't copy that line; we only copy/paste "No" so that we aren't duplicating any data transfers. Can this be done?

Thank you!

 

[chrishlee1228]

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

Regarding this code is there a way to copy all sheets based on tab names listed in another tab of the macro workbook? For example the list in a tab labeled "tabs" contains a list of tabs that I want copy and pasted: "Sheet1", "Sheet2", etc. instead of having to update it in the VBA screen every time there is a new updated tab?

Also I want a conditional copy/paste where if in my column labeled "Already Pasted" for each line of data with a string text has a "Yes" then we don't copy that line; we only copy/paste "No" so that we aren't duplicating any data transfers. Can this be done?

Thank you!

Data file:

Tabs to copy paste:

 

[Joe4]

Regarding this code is there a way to copy all sheets based on tab names listed in another tab of the macro workbook? For example the list in a tab labeled "tabs" contains a list of tabs that I want copy and pasted: "Sheet1", "Sheet2", etc. instead of having to update it in the VBA screen every time there is a new updated tab?

Also I want a conditional copy/paste where if in my column labeled "Already Pasted" for each line of data with a string text has a "Yes" then we don't copy that line; we only copy/paste "No" so that we aren't duplicating any data transfers. Can this be done?

Unless you have a question about the original question or solution posted (and aren't changing anything or introducing something new), it is usually much better to post your question to its own thread, than it is to dredge up an old thread. That way, it will appear in the "Unanswered threads" listing, which means it will get a lot more looks, and has a greater chance of being answered.

You can always include links to other threads in your question, if you feel that might be helpful.