I've been helping another user create a workbook that dynamically adds, renames and deletes worksheets from a "Main Sheet".
I have got the whole thing figured out and running to satisfaction, except...
The macro runs fine if the Visual Basic Editor is open. If the editor is closed while the macro is run, I get "Runtime Error '9': Subscript out of range"
Any ideas what could be casing this? The errors occur when attempting to add sheets.
Here's my "UpdateAll" macro that is having the issues:
Sub UpdateAll()
Dim SheetsAddRename As Range
Dim shts As Worksheet
Dim SheetTest As String
Dim RowsDelete As Range
For Each SheetsAddRename In Sheets("Main Sheet").Range("C2:C22")
SheetTest = False
If SheetsAddRename = "" Then
Sheets("Main Sheet").Activate
Call DeleteRows
Call Resync
Exit Sub
Else
For Each shts In ThisWorkbook.Worksheets
If shts.CodeName = SheetsAddRename.Offset(0, -1) And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value = "" Then
shts.Name = SheetsAddRename
SheetTest = True
ElseIf shts.CodeName = SheetsAddRename.Offset(0, -1) And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value <> "" Then
Sheets(SheetsAddRename.Value).Delete
SheetTest = True
End If
Next shts
If SheetTest = False And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value = "" Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = SheetsAddRename.Value
SheetsAddRename.Offset(0, -1) = ActiveSheet.CodeName
End If
End If
Next SheetsAddRename
End Sub
It seems to break-down here:
If SheetTest = False And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value = "" Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = SheetsAddRename.Value
SheetsAddRename.Offset(0, -1) = ActiveSheet.CodeName
End If
When it goes to update the Main Sheet with the new worksheet's codename, ActiveSheet.CodeName seems to be blank.
Again, this only happens if the Visual Basic Editor is not open. It is as if excel cannot get the codename of a sheet if the editor is not open.
Any help would be much appreciated.
Bookmarks