Macros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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, 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


Nick Hodge said:
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
Click to expand...
 
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



DMK said:
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.
Click to expand...
 
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
 
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 D14:D15 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("B22:D23").ClearContents
.Range("D26").ClearContents
.Range("D28:D29").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
 
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("B24:D25").ClearContents
.Range("D26").ClearContents
.Range("D28:D29").ClearContents Next wks
'If wks.Name <> "Rice 34" Then .Range("C43").ClearContents
.Range("B48:D48").ClearContents
.Range("D49").ClearContents
.Range("D51:D52").ClearContents
Click to expand...
Next wks
etc....Rice5... Burn15..etc
.Protect
End Sub
Click to expand...

Again thank you for helping on this project,,
Diane
 
Back
Top