Solved: Code Creating Error

[zoom38]

The following code creates an error when activated. It worked a couple of days ago and now for an unknown reason I receive a "run time error 2004 application-defined or object defined error" on the line
.Range("d1").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!H1+1"

The only changes I have made are lower in the code which has worked.

Sub CreateNewWorksheets()
Dim wsName
wsName = Sheets(Sheets.Count).Name
With CreateObject("VBScript.RegExp")
.Pattern = "\'\d{2}$"
If .Test(wsName) Then
Set mItem = .Execute(wsName)
myyr = CInt(Replace(mItem.Item(0), "'", ""))
Sheets(Sheets.Count).Copy after:=Sheets(Sheets.Count)
With ActiveSheet
.Name = "Sep '" & Format(myyr, "00") & " - Aug '" _
& Format(myyr + 1, "00")
.Range("d1").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!H1+1"
.Range("a8").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!A34"
.Range("q3").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!R34"
.Range("t3").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!U34"
.Range("w3").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!X34"
.Range("aa3").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!AB34"
Range("q3").Formula = "= if('" & _
Application.Substitute(wsName, "'", "''") & "'!O34 = """","""",if(('" & _
Application.Substitute(wsName, "'", "''") & "'!R34) >320, 320, '" & _
Application.Substitute(wsName, "'", "''") & "'!R34))"
.Range("b8:o34").Locked = False
.Range("b8:o34").ClearContents
End With
End If
End With
End Sub


Thanks
Gary

[Zack Barresse]

Hi Gary,

Change this ..

Application.Substitute

.. to this ..

Application.WorksheetFunction.Substitute

HTH

[zoom38]

I tried it but I still get the same error message on the same line. Should this sub be in module 1 or Thisworkbook??

[Zack Barresse]

Should be in a standard module.

[zoom38]

Ok, that is where I have it.

[zoom38]

Sorry for wasting your time. It was my stupidity I had the wrong macro assigned. Sorry.
Please mark this thread solved or remove it.

Thanks
Gary