Run Time Error when Selecting Cell VBA

[Cassie_H]

Hello all,

Merry Christmas and Happy New years!
I am having an issue with VBA Scripting. This is what I've done:

Sub Create_Dynamic_Forecast()
'
' Create_Dynamic_Forecast
Application.ScreenUpdating = True
'ActiveWorkbook.Save
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Dynamic Forecast"
Sheets("Emergency Proforma").Select
Cells.Select
Range("B1").Activate
Selection.Copy
Sheets("Dynamic Forecast").Select
ActiveSheet.Paste
ActiveWindow.DisplayGridlines = False
ActiveSheet.Shapes("Button 1").Select
Application.CutCopyMode = False
Selection.Delete
Application.CutCopyMode = False
Range("C31").Select
Selection.ClearContents
Range("D2:K2").Select
ActiveCell.FormulaR1C1 = "30 Day Dynamic Forecast"


Application.ScreenUpdating = True
MsgBox ("Dynamic Forecast saved to C:\Temp")

End Sub

It's working until Range("C31").Select and then it bombs out with:

"Run-time error '1004': Application-defined or object-defined error"

I understand that the error relates to forms, controls, and so on but not why its happening. I just want to clear the contents of the cell on the new sheet.

Believe it or not, i'm not very good at VBA!

 

[Michael M]

Hi Cassie and Welcome to the Board
Try

Sub Create_Dynamic_Forecast()
'
' Create_Dynamic_Forecast
Application.ScreenUpdating = False 'should false at beginning of code
ActiveWorkbook.Save
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Dynamic Forecast"
Sheets("Emergency Proforma").Range("B1").Copy Destination:=Sheets("Dynamic Forecast").Range("B1")
ActiveWindow.DisplayGridlines = False
ActiveSheet.Shapes("Button 1").Delete
Application.CutCopyMode = False
Sheets("Dynamic Forecast").Range("C31").ClearContents
Range("D2:K2").Value = "30 Day Dynamic Forecast"
Application.ScreenUpdating = True
MsgBox ("Dynamic Forecast saved to C:\Temp")
End Sub

 

[Cassie_H]

Thank you so much for replying, but it's still not working! I tried to cut and paste your response and I got

"Run-time error '5':
Invalid procedure call or argument"

So then I stole the last bit of yours and made this:

Sub Create_Dynamic_Forecast()
'
' Create_Dynamic_Forecast
Application.ScreenUpdating = True
'ActiveWorkbook.Save
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Dynamic Forecast"
Sheets("Emergency Proforma").Select
Cells.Select
Range("B1").Activate
Selection.Copy
Sheets("Dynamic Forecast").Select
ActiveSheet.Paste
ActiveWindow.DisplayGridlines = False
ActiveSheet.Shapes("Button 1").Select
Application.CutCopyMode = False
Selection.Delete
Application.CutCopyMode = False
Sheets("Dynamic Forecast").Range("C31").ClearContents
Range("D2:K2").Value = "30 Day Dynamic Forecast"


Application.ScreenUpdating = True
MsgBox ("Dynamic Forecast saved to C:\Temp")

End Sub

And I got my 1004 error again. I might be a bit thick!

 

[Michael M]

Try that, note to change the file path and name to suit

Sub Create_Dynamic_Forecast()
'
' Create_Dynamic_Forecast
Application.ScreenUpdating = False 'should false at beginning of code
ActiveWorkbook.SaveCopyAs Filename:="C:\Test\CopyBook.xls" ' Change path and name to suit
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Dynamic Forecast"
Sheets("Emergency Proforma").Range("B1").Copy Destination:=Sheets("Dynamic Forecast").Range("B1")
ActiveWindow.DisplayGridlines = False
ActiveSheet.Shapes("Button 1").Delete
Application.CutCopyMode = False
Sheets("Dynamic Forecast").Range("C31").ClearContents
Range("D2:K2").Value = "30 Day Dynamic Forecast"
Application.ScreenUpdating = True
MsgBox ("Dynamic Forecast saved to C:\Temp")
End Sub

 

[Cassie_H]

Great! Thanks so much for your help!

 

[Michael M]

You in NSW, Australia Cassie ??