I have a code that I use to "clean up" a workbook that I use for work. I've been using it for years and all of a sudden I got a Run Time Error. I'm not sure if it's something that I did or a software update. Any help would be appreciated as I'm not very proficient at vba code. The error is highlighted in red below, according to excel.
The error is as follows:
Run-time error '-2147319767 (80028029)
Automation error
Invalid forward reference, or reference to uncompiled type.
Code being used:
CleanUp Macro
Dim answer As Integer
answer = MsgBox("WARNING!" & vbLf & "This will clear all data." & vbLf & "Continue?", _
vbExclamation + vbYesNo, _
"WARNING - This will clear data")
If answer = vbNo Then Exit Sub
Sheet3.Select
Range("B1").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("B5").Select
Sheet1.Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("31:31").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("B5").Select
Sheet6.Select
ActiveWindow.SelectedSheets.Delete
Sheet1.Activate
Generally when I've seen this nonsensical error, the solution involves doing something to force a recompile of the VBA project. For example, add a new reference (using Tools-References in the VB Editor), then save, close and re-open the workbook. If that fixes it, you can usually remove the reference you added and save again.
@Velouria: Thanks so much! I had a similar error and your solution worked. I had spent two full days trying to trouble-shoot.
Hi Kathy,
Glad it was useful to you. 🙂
