Recently my friend in Tasmania – Valerio – was kindly helping someone online (the kindness of strangers). The problem was as follows. The person had a master list and wanted to create child sheets from this master list. Sort of a parent to child type exercise where a single list will produce multiple sheets (one to many - see image above). The problem arises when you have some sheets which have already been created and some new sheets which need to be created. As such we need to test for the existence of the sheet. In the following article I explore the VBA code required to test if a sheet exists without the use of a traditional custom function.
This is a very efficient method. Next there is the complication that the list being created is based on staff numbers. This problem needs to be addressed as creating new sheets as numbers will not work without turning the text to a string. To get around this the Cstr function will convert a number to text for us. The following is the example we use.
The code works by adding an advanced filter on column C and adding the contents of the output to an array (ar). I then loop through the array, assessing each item in the array to determine if the sheet has already been created. If not create a fresh sheet. Then autofilter the data based on the unique name and send to the desired sheet. When broken down like this it is a simple procedure which runs nicely.
So the following is the full code.
Option Explicit
Sub NewSheetsSmallman()
Dim ar As Variant
Dim i As Integer
Dim lr As Long
Dim ws As Worksheet
Dim sh As Worksheet
Application.ScreenUpdating = False
lr = Range("A" & Rows.Count).End(xlUp).Row 'Trap the last row
Range("C1:C" & lr).AdvancedFilter 2, [P1], , 1 'Unique records only
ar = Range("P2", [P65536].End(xlUp)) 'Assign to array
Set sh = Sheet1 'Change to suit
For i = LBound(ar) To UBound(ar) 'Start the loop
If Not Evaluate("ISREF('" & ar(i, 1) & "'!A1)") Then 'Check sheet exists
Worksheets.Add(After:=Sheets(Sheets.Count)).Name = ar(i, 1) 'Add sheet if False
End If
Set ws = Worksheets(CStr(ar(i, 1))) 'Assign ws variable to sheet.
sh.Range("C1:C" & lr).AutoFilter 1, ar(i, 1) 'Filter
sh.[C1].CurrentRegion.Copy ws.[a1] 'Transfer the data
Next i
sh.[B1].AutoFilter ' Clean up after this point
Application.ScreenUpdating = True
sh.Select 'creating new sheets puts cursor on those sheets, otherwise no need to select.
sh.[P1:P100].Clear 'Clear Col P.
End Sub
I have attached an example Excel file to show the workings. Note it will work if some of the sheets exist or none of the sheets exist. This post pulls together a lot of information on thesmallman.com.
