The below code works to create worksheets from a dynamic named range "classes". I am trying to add header rows, another named range, as the worksheet is created. The macro does copy the named range "headerrows" but doesn't paste even though the worksheet is correctly named and created.
Any ideas how to properly reference the newly created worksheet??
Sub AddSheets()
Dim xRg As Excel.Range
Dim wSh As Excel.Worksheet
Dim wBk As Excel.Workbook
Dim Classes As String
Set wSh = ActiveSheet
Set wBk = ActiveWorkbook
Application.ScreenUpdating = False
For Each xRg In wSh.Range("Classes")
With wBk
.Sheets.Add after:=.Sheets(.Sheets.Count)
Debug.Print Range("headerrows").Address
On Error Resume Next
ActiveSheet.Name = xRg.Value
Range("headerrows").Copy ActiveSheet("a1").PasteSpecial
If Err.Number = 1004 Then
Debug.Print xRg.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next xRg
Application.ScreenUpdating = True
End Sub
Bookmarks