Combine multiple worksheets into one sheet while excluding some worksheets

Question

I have 5 people that each maintain their own Excel file. The files all have a tab that I care about with the same data structure (columns) and more 20,000 rows each. I need to pull that tab from each sheet, and not any other tabs they may have, into a master sheet.

To do this, I have created a master workbook and added a Data Link to each of the individual workbooks, and created a tab pulling the data I want from the individual workbooks. I then use the below VBA script to create a master tab that combines all of the data from my created tabs. If it matters, all of these files are hosted as documents in SharePoint.

Sub Master()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Master"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

My question is two fold.

First, is there a better way to do this with lower overhead? We have 40 columns and over 100,000 rows, so the sheet moves at a snails pace and regularly locks up. (we are building a non-excel solution but we need this to work until that is done)

Second, is there a way that I can edit the above script to 1. Continue to update the created Sheet? And 2. exclude specific tabs in my sheet, like ones named reports and dashboard?

Answer 1

Below written code will help you to copy Specific Sheets to Mater Workbook.

Sub CopySheets()

Dim sh as Worksheet,  wb as Workbook

Set wb = Workbooks("Target Workbook")
For Each sh in Workbooks("Source Workbook").Worksheets(Array("sheet1","sheet2"))
sh.Copy After:=wb.Sheets(wb.sheets.count)

Next sh

End Sub

Note:

• You can edit this VBA code to add as many Worksheets to copy, as an Array. Worksheets(Array("sheet1","sheet2")).

• In case you want to Copy the entire Workbook the fastest method I can suggest you is,

  ThisWorkbook.saveas Filename:=NewFileNameWithPath, Format:=xlOpenXMLWorkbook