A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old October 18th 07, 03:13 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default how to make a macro to clear multiple cells from multiple worksheets?

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???

Ads
  #2  
Old October 18th 07, 03:30 PM posted to microsoft.public.excel.worksheet.functions
Gary''s Student
external usenet poster
 
Posts: 11,059
Default how to make a macro to clear multiple cells from multiple workshee

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

--
Gary''s Student - gsnu2007


" wrote:

> 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???
>
>

  #3  
Old October 18th 07, 04:31 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 22,912
Default how to make a macro to clear multiple cells from multiple worksheets?

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

On Thu, 18 Oct 2007 07:13:38 -0700, wrote:

>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???


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MACRO AND MULTIPLE WORKSHEETS Mel Excel Worksheet Functions 8 January 21st 07 02:53 PM
macro copy/paste data from multiple cells to multiple cells Diana Excel Discussion (Misc queries) 0 July 10th 06 09:24 PM
Use a macro on multiple Worksheets Bwoods Excel Discussion (Misc queries) 1 March 19th 06 11:20 PM
make multiple cells in 1 worksheet equal multiple cells in another riley454 Excel Worksheet Functions 1 January 19th 06 03:00 PM
adding certain cells in multiple worksheets in multiple workbooks Stephen via OfficeKB.com Excel Worksheet Functions 1 February 4th 05 08:31 PM


All times are GMT +1. The time now is 12:00 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.