mult. w/sheets need identical view when opening (range, mag, cell)

[Peter]

Dear Excel Gurus,

I often work with large multiple spreadsheets in a given workbook. The
spreadsheets have identical structure within each book. They differ from
each other only in respect of the data content of each sheet.

My productivity would be increased immeasurably if I could issue a 'global
command' to apply to a given workbook which does three things which will
enable each worksheet to open with an identical view and with identical cell
selection position:
--set all worksheets view to the same magnification
--set all worksheets view to the same cell range
--position the cursor or a selected cell in the same position in each
worksheet.

The 'global' command would need to be resettable in any given session of my
working on the same workbook, so that I could for example, work on one area
of the sheets in one magnification and then change the setting so as to work
on another area of each worksheet at another magnification and etc.

I would be very grateful if someone could suggest a command I could use.

Regards,

Peter

 

[Dave Peterson]

You can create a macro that runs each time excel opens that workbook. And you
can call that macro anytime you want (tools|macro|macros, select it and click
run).

This may give you a start:

Option Explicit
Sub autpen()
Dim wks As Worksheet
Dim myZoom As Long
Dim myAddr As String

myZoom = 80
myAddr = "C99"

For Each wks In ThisWorkbook.Worksheets
Application.Goto wks.Range(myAddr), scroll:=True
ActiveWindow.Zoom = myZoom
Next wks

ThisWorkbook.Worksheets(1).Select
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Peter]

Thankyou dave,
I will try your suggestion. I dont know anything about macros but now is my
chance to learn! Thanks for the tip about where to get info on macros.
Cheers,
Peter