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

I’ve created some dummy data files for you to download and practice with. Control Sheet = Macro Control Sheet.xls Source Data = M3Flex.xls

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.