How to clear contents of an Excel Workbook through vba

Question

I'm using a master worksheet to automate data. I want a message box to display and clear the contents of particular columns in tables in one of the worksheets when I start the workbook. The "Application-defined or object-defined error" keeps occurring. The code for my "This workbook" section is as follows:

Option Explicit

Sub Workbook_Open()

Dim answer As Integer

answer = MsgBox("Do you want to clear the totals?", vbYesNo + vbQuestion, "Clear Totals")

If answer = vbYes Then

Call Sheet1.ClearContents_1

End If

End Sub

This is my Sheet1 code:

Sub ClearContents_1()

Call Loop_Clear_C

Call Loop_Clear_M

Call Clear_S

End Sub

Sub Loop_Clear_C()

For i = 1 To Range("UserTable79").Rows.Count
    Range("UserTable79[Total]")(i) = 0
Next i
End Sub

Sub Loop_Clear_M()

For i = 1 To Range("ServiceTable79").Rows.Count
    Range("ServiceTable79[Total]")(i) = 0
Next i
End Sub

Sub Clear_S()

Range("TotalTable79[Actual Total]").ClearContents

End Sub

They worked separately but not together. The msg box comes up but doesn't run the Sheet1 code. How do I call upon this sheet code?

Answer 1

When the range you're referring to doesn't exist, the "Application-defined or object-defined error" notice frequently appears. When you activate Sheet1 by clicking on it, try using the Immediate panel to execute an operation on the ranges you're referring to (try entering Range in the Immediate panel, for example) ("UserTable79"). Get an error when you select)?

When calling a function from the "ThisWorkbook" section, the worksheet must be specified explicitly (for example, if the worksheet is named "SheetName," you must specify Call ThisWorkbook).

Sheets("SheetName").

It may be easier to use ClearContents 1 rather than Call Sheet1.ClearContents 1).