I have to define 24 named ranges on a annual worksheet. The range names have to be deleted from last years sheet and installed on the new sheet for this year. The deletion code works but the following code for the new sheet fails with a compile error expected end of statement at line 10
Sub NameRngs(NewYr)
Dim myNamedRange As Range
Dim myRangeName As String
Set myWorksheet = "ManagementAccounts_" & NewYr
x = 1
For a = 0 To 240 Step 20
10 Set myNamedRange = myWorksheet.Cells(1, 11 + a),.Cells(1, 19 + a))
myRangeName = "_Formulas" & x
ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRange
Set myNamedRange = myWorksheet.Range(.Cells(7, 11 + a), .Cells(67, 19 + a))
myRangeName = "_Results" & x
x = x + 1
ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRange
Next a
End Sub
I believe that if unspecified range has a workbook scope but the worksheet is essential too.
Can someone show me where I am going wrong please
John
Bookmarks