So, I've written some code that reads through a list of sheets, and in
the event a given sheet can NOT be found in the workbook, the code
copies a "Template" sheet and names it after the missing sheet.

The code runs beautifully the first time. Any time there is a missing
sheet on the list, the code copies the "Template" sheet and renames
it, accordingly.

Where the code runs into problems is upon sheet deletion. If I select
a range of sheets (manually) and delete them from the workbook, the
code is unable to access the "Copy Worksheets" method upon re-run.

I have seen others post about this bug in Excel and am wondering if
anyone knows/has a work-around.

Here is the code. It is a bit complicated as it uses Global
Variables, but hopefully the names make sense. Please ask questions
or offer advice. Thanks, Thaddeus

--------------------


Option Explicit
Option Base 1 ''set the first array address to 1 instead
of zero.

Sub ADD_TEMPLATE_SHEETS()
'
' Written 5/25/2007 by tagar
'

Dim cntsheet As Long '''The count of sheets in the book at the
start of sub.
Dim cntsheetplus As Variant '''A counter on the increment of
sheets, used to add new sheets to the end of the book.
Dim nmSheet As Variant '''used to iterate through cntsheets
Dim nmAddSheet As Variant '''the name of the sheet to check and add
Dim addTarget As Variant '''target boolean to determine if sheet
exists
Dim i As Long '''Page counter for lngRowMax
Dim j As Long '''Page counter for lngColMax
Dim lngRowCount As Long '''the counter column number
Dim lngColCount As Long '''the actual column number
Dim sheetArray() As Variant '''
Dim arrayIndex As Long


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
'''
'''Get Global Variables!!
'''
PROJECT_GLOBALS.GET_GLOBALS

Workbooks(nmWorkbook).Activate

cntsheet = Workbooks(nmWorkbook).Worksheets.Count '''Get the count of
worksheets in the workbook
cntsheetplus = cntsheet '''assign the initial
value to cntsheetsplus


''''enter all sheet names into an array
ReDim sheetArray(1 To cntsheet + 10)
For nmSheet = 1 To cntsheet
sheetArray(nmSheet) = Sheets(nmSheet).Name
Next nmSheet

lngColCount = Worksheets(nmConsoleSheet).Cells(24,
6).End(xlToRight).Column - 5
lngRowCount = Worksheets(nmConsoleSheet).Cells(24, 5).End(xlDown).Row
- 24


For i = 1 To lngRowCount '''for each sheet to add (row)
For j = 1 To lngColCount '''for each data type (column)
addTarget = 0
'''Display Work Update Status
Sheets(nmConsoleSheet).Activate
Sheets(nmConsoleSheet).Cells(19, 4).Value = "Checking " & nmAddSheet
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.DisplayAlerts = False
Application.ScreenUpdating = False

For arrayIndex = 1 To cntsheet '''check to see if sheet already exists
in workbook.


'''Assemble the name of the sheet to check/add if missing
If arrayIndex = 1 Then
nmAddSheet =
Workbooks(nmWorkbook).Sheets(nmConsoleSheet).Cells (i + 24, 5).Value &
"-" & Sheets(nmConsoleSheet).Cells(24, j + 5).Value
End If

If sheetArray(arrayIndex) = nmAddSheet Then
addTarget = 1 ''''Get the name if sheet exists
arrayIndex = cntsheet
'MsgBox ("Do Nothing")
End If


Next arrayIndex '''check all sheets to see if nmAddSheet
already exists. If yes, do nothing.


If addTarget = 0 Then
Workbooks(nmWorkbook).Sheets(nmTemplateSheet).Copy
After:=Workbooks(nmWorkbook).Sheets(Sheets.Count) '(cntsheetplus)
Workbooks(nmWorkbook).Sheets(nmTemplateSheet &
" (2)").Name = nmAddSheet
cntsheetplus = cntsheetplus + 1 '''since the sheet
was added, increment cntsheetsplus so the next sheet will still be
added to the end of the workbook.
End If

Next j '''continue for each Data Type (columns)
Next i '''continue for all sheets to be checked/added (rows)



'''Display Work Update Status
Sheets(nmConsoleSheet).Activate
Sheets(nmConsoleSheet).Cells(19, 4).Value = "Complete"
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub