how to make a macro to clear multiple cells from multiple worksheets?

  • Thread starter pipenpaddleopsakopalus
  • Start date
P

pipenpaddleopsakopalus

i've got a protected document (can be unprotected if need be) in which
my employees fill out emission events daily. every day they must go
through every worksheet in the workbook to erase everything entered
the previous day. if they miss something it causes some reporting
errors. how can i create a macro which clears out the specific cells
i select from every worksheet in the workbook???
 
G

Guest

Your application is a poster child for using the Macro Recorder:

1. go to the first sheet
2. switch on the Recorder
3. carefully clear all the required cells in all the required sheets
4. switch off the Recorder
 
G

Gord Dibben

Protected in which manner? Sheets only? Workbook only? File to open only?

Or a combination of the above?

Where are you storing the data they enter before they erase it?

I would suggest that a Template with Data Tracking may be better suited for what
you are doing.

But if sheet protection is enabled and password same for all sheets...........

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
With Sheets(n)
.Unprotect Password:="justme"
.Range("A1:A10").ClearContents
.Protect Password:="justme"
End With
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top