zoom38
04-18-2006, 06:21 PM
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
.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