I have a template workbook (Temp_TS) that I use to create new files (based on another template) and want to then run vba code contained in Temp_TS against the new file. As all of the VBA is contained in Temp_TS, how do I specify that this should be executed against the new file I have created?
An example of one of the procedures that I will run against the new file is shown below:
Sub DelSheets(Optional p_sheet_to_keep1 As String = "xxx", _
Optional p_sheet_to_keep2 As String = "yyy", _
Optional p_sheet_to_keep3 As String = "zzz")
On Error GoTo DelSheets_err
' e.g. call gen.DelSheets("Parameters", "Chris Prosser")
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If LCase(ws.Name) <> LCase(p_sheet_to_keep1) And _
LCase(ws.Name) <> LCase(p_sheet_to_keep2) And _
LCase(ws.Name) <> LCase(p_sheet_to_keep3) Then
ws.Delete
End If
Next
Application.DisplayAlerts = True
DelSheets_exit:
Exit Sub
DelSheets_err:
Debug.Print "Error: ", Err.Number, vbCrLf, Err.Description
Resume Next
End Sub
To execute this against the current workbook I would just do something like:
gen.DelSheets("Parameters", "Chris Prosser")
How would I specify that I want this to execute against the new workbook and not the current workbook?
EDIT
In case it helps anyone here is the method used to create the new sheet:
strCommand = "cp """ & oldFileName & """ """ & newFileName & """"
Shell strCommand, 1
This part works fine, the file is created but not opened yet.