R/T error 1004

[jim may]

Can anyone spot my problem? R/T occuring a/on Red Line...

Sub Foo1()
For i = 1 To ActiveWorkbook.Names.Count
  CAddress = Mid(ActiveWorkbook.Names(i), WorksheetFunction.Find("!", ActiveWorkbook.Names(i)) + 1, 12)
  FullNameData = Names(i).Name
  FullNameData = WorksheetFunction.Substitute(FullNameData, "Block1", "Block2", 1)
  FullNameData = WorksheetFunction.Substitute(FullNameData, "b1day", "b2day", 1)
  PartNameData = Left(FullNameData, WorksheetFunction.Find("!", FullNameData) - 1)
  Worksheets(2).Names.Add Name:=PartNameData & "!" & CAddress 
 '.......
Next
End Sub

Thanks in Advance...

Jim

 

[shg]

There should be a (legal) name and a refersto argument. See Help for the Add method as it refers to Names.

 

[jim may]

Got it, Final Code is:

Sub DupRangeNames() 'assumes pre-existence of sheets: Front Page Block1 and Front Page Block2
Dim Cadd As String, FullNameData As String
For i = 1 To ActiveWorkbook.Names.Count
  Cadd = ActiveWorkbook.Names(i)
  Cadd = WorksheetFunction.Substitute(Cadd, "Block1", "Block2")
  FullNameData = Names(i).Name
  FullNameData = WorksheetFunction.Substitute(FullNameData, "Block1", "Block2", 1)
  FullNameData = WorksheetFunction.Substitute(FullNameData, "b1day", "b2day", 1)
  ActiveWorkbook.Names.Add Name:=FullNameData, RefersTo:=Cadd
Next
End Sub