Ruede
09-08-2023, 10:45 AM
Hi Everyone
I would really appreciate some help with debugging the attached VBA code (created in Excel 2021) and getting the last sorting function to work.
The purpose of the code is to:
(1) create new worksheets based on a template (named aa_template) and with names that correspond to the list in the first column of the active worksheet (aa_Master),
and then...
(2) sort the worksheets alphabetically.
The code mostly works. It creates new worksheets based on the template and list of names, but... it :
- throws up an "Execution error 1004: Name' method of object '_Worksheet' failed" and halts
- does not sort the worksheets alphabetically.
Thankyou in advance
Ruede
-----------
Sub Create()
Dim myArray As Variant, rng As Range, rngLoop As Range, ws As Worksheet, ShCount As Integer, i As Integer, j As Integer
'Error warning: missing template
If Not SheetExists("aa_Template") Then
MsgBox "The Template sheet does not exist. Make sure the Template is included before processing.", vbCritical + vbOKOnly
Exit Sub
End If
'Optimise code
Application.ScreenUpdating = False
'Create new worksheets from names in first column of aa_Master worksheet
With ActiveWorkbook.Sheets("aa_Master")
Set rng = .Range("B2", "B" & .Cells(Rows.Count, "B").End(xlUp).Row)
For Each rngLoop In rng
If Not SheetExists(rngLoop.Value) Then
ActiveWorkbook.Sheets("aa_Template").Copy after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Set ws = ActiveSheet
ws.Name = rngLoop.Value
Else
Set ws = ActiveWorkbook.Sheets(rngLoop.Value)
End If
Next
.Activate
End With
'Sort worksheets alphabetically
ShCount = Sheets.Count
For i = 1 To ShCount - 1
For j = i + 1 To ShCount
If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
Dim sht As Worksheet
If wb Is Nothing Then Set wb = ThisWorkbook
On Error Resume Next
Set sht = wb.Sheets(shtName)
On Error GoTo 0
SheetExists = Not sht Is Nothing
End Function
I would really appreciate some help with debugging the attached VBA code (created in Excel 2021) and getting the last sorting function to work.
The purpose of the code is to:
(1) create new worksheets based on a template (named aa_template) and with names that correspond to the list in the first column of the active worksheet (aa_Master),
and then...
(2) sort the worksheets alphabetically.
The code mostly works. It creates new worksheets based on the template and list of names, but... it :
- throws up an "Execution error 1004: Name' method of object '_Worksheet' failed" and halts
- does not sort the worksheets alphabetically.
Thankyou in advance
Ruede
-----------
Sub Create()
Dim myArray As Variant, rng As Range, rngLoop As Range, ws As Worksheet, ShCount As Integer, i As Integer, j As Integer
'Error warning: missing template
If Not SheetExists("aa_Template") Then
MsgBox "The Template sheet does not exist. Make sure the Template is included before processing.", vbCritical + vbOKOnly
Exit Sub
End If
'Optimise code
Application.ScreenUpdating = False
'Create new worksheets from names in first column of aa_Master worksheet
With ActiveWorkbook.Sheets("aa_Master")
Set rng = .Range("B2", "B" & .Cells(Rows.Count, "B").End(xlUp).Row)
For Each rngLoop In rng
If Not SheetExists(rngLoop.Value) Then
ActiveWorkbook.Sheets("aa_Template").Copy after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Set ws = ActiveSheet
ws.Name = rngLoop.Value
Else
Set ws = ActiveWorkbook.Sheets(rngLoop.Value)
End If
Next
.Activate
End With
'Sort worksheets alphabetically
ShCount = Sheets.Count
For i = 1 To ShCount - 1
For j = i + 1 To ShCount
If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
Dim sht As Worksheet
If wb Is Nothing Then Set wb = ThisWorkbook
On Error Resume Next
Set sht = wb.Sheets(shtName)
On Error GoTo 0
SheetExists = Not sht Is Nothing
End Function