PDA

View Full Version : insert the worksheet if the sheet is missing



uktous
03-11-2012, 03:32 AM
Hi,

I want a macro which can perform the following function.

Sheet A, Sheet B and Sheet C must appear in the workbook.
If any one of them (or some of them) is missing, insert the sheet.

Could you please write me the macro?

Thanks

Paul_Hossler
03-11-2012, 06:30 AM
Assuming that Sheet A, etc is merely another blank worksheet ....


Option Explicit

Sub Check()
Call InsertMissingSheet("Sheet A")
Call InsertMissingSheet("Sheet B")
Call InsertMissingSheet("Sheet C")

End Sub

Sub InsertMissingSheet(sSheetName As String)
Dim i As Long

i = 0
On Error Resume Next
i = ThisWorkbook.Worksheets(sSheetName).Index
On Error GoTo 0

If i > 0 Then Exit Sub

ThisWorkbook.Worksheets.Add.Name = sSheetName
End Sub


If Sheet A, etc. is a specially formated, preset, worksheet, it gets a little more complicated

Paul