A while ago I published a post that demonstrated how to use a macro to generate multiple pdf files for distribution, by working through a filtered list. I’ve recently acquired a macro solution to do something similar but in this case it generates a series of excel spreadsheets from the original file rather than pdf.
In this blog post I will show you how this macro can be used to split a large workbook into multiple smaller workbooks based on the value in a filtered field. In this case we will split a monthly contract monitoring spreadsheet into multiple versions based on the commissioner (or CCG) code.
What is the Problem we are Trying to Solve?
In this case we have a large spreadsheet with activity and financial data at POD and specialty level for a whole load of GP Practices and CCGs. I would like to be able to generate individual files for each CCG so that I can safely email the data out without fear of it containing data from another CCG.
I need to split the original spreadsheet based on the CCG Code field.
How does the Spreadsheet Splitting Macro Work?
The macro is quite long so I will only show extracts. You can see the full macro in the example Macro Control Sheet.
In summary it:
- Uses the Macro Control Sheet to set some constants such as workbook names and most importantly it looks here for the list of CCGs that it will attempt to generate reports for.
- When you run the main macro from this Control Sheet it will ask you to locate the source data.
- The spreadsheet splitting macro then filters the source data for each CCG in turn and copies that filtered data to a newly created blank spreadsheet that it then saves in the output folder.
- There is some additional formatting going on which generates a pivot table in each CCGs file and adds headers etc.
- Finally there is an error checker or reconciliation where the totals in each individual file are compared to the source data file.
Extracts from the Spreadsheet Splitting Macro
This first section defines the variables, names the assorted workbooks and determines the location of the source data.
Sub Split\_Spreadsheet() 'Created by unknown (but thanks) 'Amended and commented by Dr Moxie Dim mycellrow, mycellvalue As String Dim prevcellrow, prevcellvalue As String Dim endcellrow As String Dim outputlocation, Month As String Dim SourceData, Macrofile, CCGname, lastCCG, NumberOfCCGs, FileDesc, PivotTitle As String Dim lastline, LastColumn As String Dim ControlTotal As String Dim RecPivotlast As String Dim Datastart As Integer 'used to indicate the header and instruction rows Macrofile = ActiveWorkbook.Name 'this is the control spreadsheet with macro buttons NumberOfCCGs = Range("A65535").End(xlUp).Row FileDesc = Range("A7").Value 'Sets the filename stem from value entered on control sheet PivotTitle = Range("A9").Value ' Sets the Header of each output file from value entered on control sheet outputlocation = ActiveWorkbook.Path & "\\Output\\" MsgBox ("Please Select the location of the Source Data file") openname = Application.GetOpenFilename(fileFilter:="Microsoft Excel Files (\*.xlsx),(\*.xls)") Workbooks.Open (openname) SourceData = ActiveWorkbook.Name 'this is the source data file 'Next we start the loop, moving from the control sheet to the source data to filter for each CCG. 'A new blank file is created and saved and then the filtered data is copied across. 'Note I am not showing all the formatting stages in this extract - see main file for details. 'Loop through your list of CCG's 'Starting at 16 due to 15 header and instruction rows '\*\*\*\*\*Update Datastart Value According to Number of Header and Instruction Rows You Have\*\*\*\*\*\* '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Datastart = 16 For I = Datastart To NumberOfCCGs ' not actually number of CCGs but used rows 'Filter the SourceData data by CCG Code Windows(Macrofile).Activate Range("A" & I).Select CCGCode = ActiveCell.Value Windows(SourceData).Activate Sheets("data").Select Range("A1").Select Selection.AutoFilter Field:=1, Criteria1:=CCGCode lastCCG = Range("A1048576").End(xlUp).Row If lastCCG = 1 Then GoTo SkipCCG LastColumn = Range("A1").End(xlToRight).Column 'Save the filtered data to new file newxlfilename = CCGCode & FileDesc & ".xls" Workbooks.Add Application.DisplayAlerts = False Sheets("sheet2").Delete Sheets("sheet3").Delete ActiveWorkbook.SaveAs outputlocation & newxlfilename Application.DisplayAlerts = True Windows(SourceData).Activate Sheets("data").Select endcellrow = Range("a1048576").End(xlUp).Row Range("A1:AA" & endcellrow).Copy Windows(newxlfilename).Activate Sheets("Sheet1").Select Range("A1").Select ActiveSheet.Paste croppedCCGname = Left(newxlfilename, 30) ActiveSheet.Name = croppedCCGname ActiveWorkbook.Save ActiveWorkbook.Close Next I End Sub
How to Run the Spreadsheet Splitting Macro with Example Data
You can save them in any folder on your computer but you need to have a sub-folder called “output” I suggest something like this:
In order to run the macro and generate all your individual excel workbooks (one per commissioner) you need to open the Macro Control Sheet and follow the instructions.
When you run the macro it will ask you to locate the source file, which in this case is M3flex.xls
Having done this it will whizz through and create all the individual extracts in the output folder.