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

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

same thing to all sheets - use Across Worksheets

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

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

Clarification

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.

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

Re: Clarification

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

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

PS. Don't forget to protect your module

Sub Checked()