I've got a macro to consolidate data from 2 different tables in 14 different workbooks, tblstaff and tblwage. Currently, the way the code works is:
- Open source WB
- copy ListObject.DataBodyRange from each
- PasteSpecial (xlvalues) into consolidated ListObject.DataBodyRange
- Close source WB
- Next Source WB
My question: Is this the most efficient way to go about doing this? I'm not very familiar with arrays in VBA, but was wondering if I could accomplish the same by creating two 2d arrays (staff and wage) and reading each table into them, pasting the final/consolidated array at the end of the loop. Thanks in advance!
Edit: Assuming arrays are more efficient here, how do I go about creating and ReDim'ing the 2d array, reading each new table into it? Probably safe to say there's errors with the below:
Set MarRange = WBMaster.Sheets("Dashboard").Range("marcirc,mar8a")
arraysize = 0
ReDim myArray(0 To 0)
For Each MarCell In MarRange
marfile = WBMaster.Sheets("Dashboard").Range("mardir").Value & MarCell.Value
Application.StatusBar = "Importing data from " & marfile
Workbooks.Open marfile, True, True
Set WBMar = ActiveWorkbook
ReDim Preserve myArray(arraysize) As Variant
myArray = WBMar.Sheets("Staffing Details").ListObjects(1).DataBodyRange.Value
arraysize = arraysize + 1
Next MarCell
End Sub
