Macros

[DMK]

I'm working on a 10 page workbook. I have inserted a "Click here to Reset
this SHEET" on each sheet. They all work independently. NOw I must put on the
front page a larger button to CLEAR all 10 pages of data. And this is where
I'm having problems. I know I must link the individual marco to the main one
,, but haven't seemed to be able to do it.

 

[Nick Hodge]

DMK

You don't need to link the macros for each sheet, just use a for...next loop

e.g

Dim wks as Worksheet
For Each wks in ThisWorkbook
'Do what you want to each sheet here
Next wks

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @ http://www.exceluserconference.com/UKEUC.html

 

[DMK]

Nick, thanks for your reply-- however...
I need a simplified answer,, as I don't understand 'excel' language...
(I understood that I didn't have to link each macro-- that helped)
Again thanks for taking the time for me.
--
Thanks for any help you can provide.
DMK

DMK

You don't need to link the macros for each sheet, just use a for...next loop

e.g

Dim wks as Worksheet
For Each wks in ThisWorkbook
'Do what you want to each sheet here
Next wks

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @ http://www.exceluserconference.com/UKEUC.html

 

[Nick Hodge]

DMK

To help much further we may need to see the code you already have

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @ http://www.exceluserconference.com/UKEUC.html

Nick, thanks for your reply-- however...
I need a simplified answer,, as I don't understand 'excel' language...
(I understood that I didn't have to link each macro-- that helped)
Again thanks for taking the time for me.

 

[DMK]

Nick, To follow are the macros for two pages ( I shortened them to send to you)
the first one -- Rectangle3_ is the page 2 and the Rice34_Rectangle4_ is for
page 3 in the workbook (10 pages total in this workbook). I need to have a
"Reset ALL DATA button" on the title page (page 1). I know this is easy for
you-- but headaches for me..
Thanks for all your help, Diane

Sub Rectangle3_Click()
'
' Rectangle3_Click Macro
' Macro recorded 5/24/2007 by Diane Knepp
'
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.Unprotect
Range("D14:d15").Select
Selection.ClearContents
Range("c20").Select
Selection.ClearContents
Range("b22:d22").Select
Selection.ClearContents
Range("b23:d23").Select
Selection.ClearContents
Range("D26").Select
Selection.ClearContents
Range("D28:d29").Select
Selection.ClearContents
Range("d14").Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Sub Rice34_Rectangle4_Click()
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.Unprotect
Range("c20").Select
Selection.ClearContents
Range("b22:d22").Select
Selection.ClearContents
Range("b23:d23").Select
Selection.ClearContents
Range("d26").Select
Selection.ClearContents
Range("D28:d29").Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[Nick Hodge]

Diane

A few caveats first. (And use a copy of the file, not the actual one)

1) As written this will operate on ALL worksheets (you could put some code
in to exclude sheets if you need this). Remember ALL sheets include your
first sheet. (You can exclude this by changing the name in the code from
First Sheet to whatever it is and taking the single quote out at the start
of this line and the 3rd from bottom ('End If)
2) I have only written code to clear the cells of the 'common areas' (e.g.
on Rectangle3 you clear D1415 but not in Rectangle4). If these areas are
not common your code becomes a little trickier as you deal with each unique
area.
3) I have presumed when you run this the sheets are protected without a
password.

Sub RunOverSheets()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
'If wks.Name <> "First Sheet" Then
With wks
.Unprotect
.Range("C20").ClearContents
.Range("B2223").ClearContents
.Range("D26").ClearContents
.Range("D2829").ClearContents
.Protect
End With
'End If
Next wks
End Sub

That will clear the common cells from every sheet in the workbook when run.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @ http://www.exceluserconference.com/UKEUC.html

 

[DMK]

Nick,
Things are starting to make sense,, and YES each worksheet has it's own set
of cells to clear. I think I have an idea of what you are saying. Since each
page of the workbook has different cells to clear-- they are not 'common' and
need to be addressed seperate.
If I'm understanding what you siad, I then, " on page one "Title" ---I do a
macro and request it to clear the cells on page two Rice 1 & 2; then I do a
macro and request it to clear the cells on page three Rice34_ ; page 4
Rice5 ; page 5 Burn15; etc... "

Sub RunOverSheets()

Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
Name <> "Title" Then
'If wks.Name <> "Rice 1&2" Then .Unprotect
.Range("C22").ClearContents
.Range("B2425").ClearContents
.Range("D26").ClearContents
.Range("D2829").ClearContents Next wks
'If wks.Name <> "Rice 34" Then .Range("C43").ClearContents
.Range("B4848").ClearContents
.Range("D49").ClearContents
.Range("D5152").ClearContents

Next wks
etc....Rice5... Burn15..etc
.Protect

End Sub

Again thank you for helping on this project,,
Diane

 

[DMK]

Nick,
Fantastic news! "It works!"
Thank you! for all your help.
Diane

 

[Nick Hodge]

Diane

I'm not sure I helped much but well done for getting it fixed

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @ http://www.exceluserconference.com/UKEUC.html

 

[DMK]

Nick,
You got me thinking in a different direction.
5 stars for your help!
Diane