Auto fill multiple worksheets

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

Guest

I have created an Excel Workbook, made up of numerous Worksheets. These
worksheets are used to record daily information, at various locations, for
bi-weekly periods. One cell, ‘J3’ is used to establish the beginning date of
the period. The first date of the period is automatically entered in ‘C12’,
by using ‘=J3’. The next date of the period is automatically entered in cell
‘C13’ by using =IF($J$3="","",$J$3+1), the next date is auto entered in ‘C14’
using =IF($J$3="","",$J$3+2), and so on. Portions of the protected worksheet
are locked, when used in entering the information.

Would it be possible to create a new bi-weekly series of worksheets by
entering the beginning date in ‘J3’ on one page? Also is it possible to
‘Unprotect and Protect’ all worksheets by a single operation? It is
cumbersome to have to unprotect each worksheet, change the value in J3 on
each worksheet and then protect each worksheet again. Have you any
suggestions?
 
Wally

First the code to protect or unprotect all sheets.

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub

Second to have J3 change on all sheets when changed on first sheet.

Select sheet2 then SHIFT + click on last sheet to group them.

In active sheet in J3 enter =Sheet1!J3 and ENTER

Select Sheet1 and change value in J3 to change on all sheets.


Gord Dibben MS Excel MVP
 
Everything works fine in the protect/unprotect macros. I'm still having a
problem with the Second suggestion. After pressing "ENTER", I get a Dialog
Box "File not found". It wants to direct me to the file containing the
workbook. If I select sheet 1 of that file, and put the appropriate date in
J3, I get "#Ref!" in J3 of remaining sheets. I'm sure I have just missed
something.

Wally
 
My instructions assumed your first sheet was named Sheet1 and the rest were
named Sheet2 through whatever.

If the sheets are named something other than that, just select second through
last by SHIFT + Click on those two tabs.

The active sheet will be the sheet that is visible.

Select J3 and type in Sheetname!J3 where Sheetname is name of the unselected
sheet.

To ungroup the sheets select the unselected sheet.


Gord
 
Make that =Sheetname!J3

If the Sheetname has spaces enter ='Sheet name'!J3

Note the two added apostrophes.


Gord
 
Gord,
Thanks, I think everything is working ok now. You guys are great and really
appreciated.
Wally
 
Back
Top