Multiply this by 12 for each month of the year and you start to have quite an horrendous copy and paste task if you want to pull it all together manually.
I’ve been working on some VBA macro code to pull the data range from each tab of the monthly return and then stack them on top of each other.
Name path = "K:\UKSW CS Bom Expections\CS_BOM_Corrections\Archive" Rowof Copy Sheet = 2 Application. Screen Updating = False Set sht Dest = Active Workbook.
And Kutools for Excel is a very handy tool, why do not free install it to have a try? Open(Filename:=path & "\" & Filename) Set Copy Rng = Wkb. Range(Cells(Rowof Copy Sheet, 1), Cells(Cells(Rows. File System Object") Msg Box str Filename & " was created on " & o FS. Date Created Set o FS = Nothing End Sub (pinched from here Then you could write a function that takes a start date and end date and returns a list of filenames...I found snippets of code all over the web but I think the original code writer was Ron de Bruin who has some excellent VBA macro examples.
Ron de Bruin offers a number of ways to work with the different workbooks, such as working through all workbooks in a set folder or looping through a user defined selection of workbooks.
Combine, and check the last option – Consolidate and calculate values across multiple workbooks into one worksheet. Then click Next, in this step, check the workbooks and sheets you need, then click to select data range.