Running macros on protected sheets

[masud8956]

Hi all!

In my workbook I have 5 sheets.

In 2 of the sheets named "DATA" and "DATA COPY" I have imported a large array of data from another folder through macro and used those data in other 3 sheets.

I kept these 2 sheets in "xlSheetVeryHidden" format to deny access to users. In "DATA" and "DATA COPY" sheet I was trying to run a macro to clear contents of the range A1:O4000 (or clear contents of the whole sheets - even better). I want the code to access these 2 sheets, clear contents of the said range (or of the whole sheets) and return to original formatting (xlSheetVeryHidden). I was trying the following code:

[/COLOR]Sub ClearMe()Application.ScreenUpdating = False
Sheets("DATA").Visible = True
Sheets("DATA COPY").Visible = True
 '
' ClearMe Macro
'


'
    Sheets(Array("DATA", "DATA COPY")).Select
    Sheets("DATA").Activate
    Cells.Select
    Selection.ClearContents
Sheets("DATA").Visible = False
Sheets("DATA COPY").Visible = False
Application.ScreenUpdating = True

End Sub[COLOR=#333333]

The code seems to take about 25-35 seconds to complete the task. But after the run is complete the sheets do not go back to the original formatting (xlSheetVeryHidden). They become "xlSheetHidden" and thus they can be easily unhidden by users which I do not want.

In fact I bumped into a very old thread (from 2002) regarding this where I took the idea of the code. Link below:

https://www.mrexcel.com/forum/excel-questions/31275-running-macros-hidden-sheets.html

What modification could be done to solve the problem? Anyone please?

Thanks in advance

 

[JoeMo]

Change these two lines:

Sheets("DATA").Visible = False
Sheets("DATA COPY").Visible = False

Replace "False" with "xlVeryHidden" (w/o the quote marks).

 

[masud8956]

@JoeMo,

Thanks a lot! It works perfectly!

Just another query, the code takes 25-30 second to complete the task. Is it normal or my code has rooms for improvement?

 

[JoeMo]

@JoeMo,

Thanks a lot! It works perfectly!

Just another query, the code takes 25-30 second to complete the task. Is it normal or my code has rooms for improvement?

You are welcome.

As to your new question, if you have formulas on the sheets you are clearing try entering:

Application.Calculation = xlCalculationManual

at the start of the code, and

Application.Calculation = xlCalculationAutomatic

just before the End Sub line.

If there are no formulas, I would try clearing the two sheets one at a time like this:

With Sheets("DATA")
      .Cells.ClearContents
End With
With Sheets("DATA COPY")
      .Cells.ClearContents
End With

 

[masud8956]

There are no formulas.

If I have understood correctly, you are advising the following code:

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

Is it right?

 

[JoeMo]

There are no formulas.

If I have understood correctly, you are advising the following code:

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

Is it right?

That's what I'm advising. We won't know if its right until you try it and post back to tell us if it reduces the run time.

 

[masud8956]

I tried it. The runtime does not reduce noticeably. Could be my machine's limitation. I am not sure.

The code looks tidy though. Thank you again for your help!