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