Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

Do same thing to all sheets in workbook

Posted by Zif on February 20, 2001 8:50 AM

Hiya

I'm sure this is fairly simple for you guys and gals with such talent!!

I need to perform the same action to every sheet in a workbook. The number of sheets in the workbook varies from week to week (usually between 20 and 40 sheets).

What I need to do is (for example) put the text "Checked" into cell A7 on every sheet.

Suggestions?

Zif

Check out our Excel VBA Resources

same thing to all sheets - use Across Worksheets

Posted by Loren on February 20, 2001 9:10 AM

Re: Do same thing to all sheets in workbook

Posted by Aladin Akyurek on February 20, 2001 9:16 AM

One way to do it:

Left lick on the tab of the first sheet, press and hold shift key, go to the tab of the last sheet and give a left click again. Then go to cell A7 on the first sheet and type "Checked". Deactivate the selected sheets by reclicking on a tab of one the selected sheets.

Aladin

Clarification

Posted by Zif on February 20, 2001 2:39 PM

SHould have pointed out that I really need to do this using VBA - worksheets are protected, so I need to unprotect each in turn, make change, reprotect (with password).

I can't do that if I select multiple sheets.

Re: Clarification

Posted by Celia on February 20, 2001 4:55 PM

Zif

Sub Checked()
Dim ws As Worksheet
For Each ws In Worksheets
With ws
.Unprotect password:="password"
.Range("A7") = "Checked"
.Protect password:="password"
End With
Next
End Sub

Celia

PS. Don't forget to protect your module

Posted by Celia on February 20, 2001 4:57 PM

Sub Checked()

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.