1

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?

  • Are you interested to Copy entire Sheet1 & 2 or is it possible to Filter records before pull them into Master Sheet? – Rajesh S Sep 21 '18 at 6:19
2

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
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.