Error msg in VBA code for clearing data field

[masud8956]

Hi everyone,

I am using the following code to clear a certain data field. I am applying the code with a button and it worked for a few days but now I am getting an error msg.

Sub ClearMe()
Application.ScreenUpdating = False
Sheets("DATA").Visible = True
Sheets("DATA COPY").Visible = True
With Sheets("DATA")
      .Cells.ClearContents
End With
With Sheets("DATA COPY")
      .Cells.ClearContents
End With
Sheets("DATA").Visible = xlVeryHidden
Sheets("DATA COPY").Visible = xlVeryHidden
Application.ScreenUpdating = True
End Sub

What could have gone wrong and how to debug it?

Regards

 

[Joe4]

What is the exact error message you are getting?
Are any of the sheets hidden when you first try running the code?

 

[afonsomira]

Did you change the name of your Sheets?

 

[masud8956]

@Joe4 The msg I am getting is:

"Run-time error '1004':
Unable to set the visible property of the worksheet class"

@afonsomira No I have not.

 

[masud8956]

@Joe4 , sorry did not answer the 2nd Question.

The sheets are hidden and I do not remember exactly if they were hidden during my 1st attempt.

 

[Joe4]

If you hit debug, which line of code does it highlight?

I don't know if this code is sufficient to unhide "very hidden" sheets:

Sheets("DATA").Visible = True

I think you need to do it as shown here:

How to make Excel worksheet very hidden and unhide it

Learn how to make an Excel sheet very hidden so that other users couldn't unhide it in the usual way, and to show very hidden sheets.

www.ablebits.com

 

[masud8956]

If you hit debug, which line of code does it highlight?

The 3rd line

Sheets("DATA").Visible = True

 

[Joe4]

The 3rd line

Then that seems to support the notion what afonsomira was suggesting, that you do not have a sheet named "Data" to unhide.

 

[Joe4]

If you try using the code found in the link I gave you to unhide all the sheets, you can what their names are:

Sub UnhideVeryHiddenSheets()
    Dim wks As Worksheet
 
    For Each wks In Worksheets
        If wks.Visible = xlSheetVeryHidden Then wks.Visible = xlSheetVisible
    Next
End Sub

 

[Fluff]

If there was no sheet called DATA I would expect an RTE 9 Subscript out of range.
@masud8956
Check that you have not protected the workbook