Here is an example for combining your branch office reports.
This example has three 'branches'
There is a Master workbook named 'HeadOffice.xls' containing Four worksheets.
One W/s named Totals and one W/s for each branch named Branch01, Branch 02 and Branch03
There are three branch workbooks: Branch01.xls etc.
Each branch Workbook contains one worksheet named BranchReport
Each branch report contains data in the range A1 to N50.
For this example Branch01.xls contains 10000 in cell A2
Branch02.xls cell A2 contains 20000 and
Branch03.xls cell A2 contains 30000
The HeadOffice.xls Workbook has a standard Visual Basic Module 'Module1' containing the following code:
Private Sub BranchCombine()
Dim strPath As String
Dim strWbName As String
Dim intBranches As Integer
Dim strBranchWs As String
Dim n As Integer
On Error GoTo ErrHnd
'set path to workbook locations - include the closing "\"
strPath = "C:\Temp\"
'set number of branches
intBranches = 3
For n = 1 To intBranches
'get branch workbook name - starts at cell A1 - n is row
strWbName = .Worksheets("Totals").Cells(n, 1)
Workbooks.Open (strPath & strWbName)
'create Branch Worksheet name
strBranchWs = "Branch" & Format(n, "00")
'copy BranchData to HeadOffice Worksheet
'Save updated HeadOffice Workbook
Each example Workbook contains a total sales value in cell A2 of the BranchReport Worksheet
The Totals worksheet in HeadOffice.xls looks like this after the macro code has run:
A B C
1 Branch01.xls 10,000.00 16.67
2 Branch02.xls 20,000.00 33.33
3 Branch03.xls 30,000.00 50.00
4 Total 60,000.00 100.00
The formulas on the HeadOffice "Totals" worksheet are:
Note the $ signs
Drag formula in C1 down two more rows
To enter the code in HeadOffice.xls:
Enter the VBA window by clicking Alt + f11 (The left Alt key and function key #11 at the same time)
In the Project Explorer window (usually on left), find VBAProject(HeadOffice.xls)
Right click on it and select Insert then Module (not Class Module)
Double click Module1 which is under the Modules folder
Enter the code in the main window.
Alternatively use the Control Toolbox toolbar to create a command button on the Totals worksheet and add the code to it (In Design mode, right click button and select view code). Enter 'Option explicit before the opening 'Private Sub CommandButton1_Click()'
and enter the code, but excluding the opening sub and the 'End Sub' as these are already in place for the button.
In the code change the path to whatever you are using in the line
strPath = "C:\Temp\"
To run the code place your cursor somewhere in the code after Sub and click f8 to single step and f5 to run
Instead of the
Workbooks("HeadOffice.xls").Saveyou could use SaveAs and create a new filename for each month or whatever.
The new filename could be created from a combination of fixed Text such as "HeadOffice" and the Month and Year, or you could include an Input box and type in the name each time.
If one of these options is of interest to you please ask and I or someone else will suggest some code.
Hope that this gives you the basics for a solution