I need to make a workbook with 52 pages (1 for each week) that looks at the
previous page and updates accordingly. Right now, when I create a new page I
have been using find/replace to replace all page references to the correct
page. This is very time consuming and I am hoping to make it quicker as I
have to make 12 files this same way - one for each person.
Any suggestions will be greatly appreciated.

Sounds like you could use Walkenbach's SheetOffset function
http://www.j-walk.com/ss/excel/tips/tip63.htm



"Lori Switzer" wrote:

I need to make a workbook with 52 pages (1 for each week) that looks at the
previous page and updates accordingly. Right now, when I create a new page I
have been using find/replace to replace all page references to the correct
page. This is very time consuming and I am hoping to make it quicker as I
have to make 12 files this same way - one for each person.
Any suggestions will be greatly appreciated.

Lori

You can EditReplace after the fact to change the sheet name which is probably
easiest.

If you're willing to use a User Defined Function.......


Function PrevSheet(rg As Range)
'Enter =PrevSheet(B1) on sheet2 and you'll get B1 from sheet1.
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Say you have 12 sheets, sheet1 through sheet12.

Select sheet2 and SHIFT + Click sheet12

In B1 enter =PrevSheet(A1)

Ungroup the sheets.

Each B1 will have the contents of the previous sheet's A1

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

On Fri, 22 Sep 2006 16:51:02 -0700, Lori Switzer
wrote:

That looks like what I need, but I have to admit I have never worked with
macros. I have typed the SHEETOFFSET Function into a new module in the
visual basic window, but I wonder if I am saving it wrong, or if it needs
compiling or linking or something because when I try to use the function in
the spreadsheet it gives me "#VALUE! ".

"JMB" wrote:

Sounds like you could use Walkenbach's SheetOffset function
http://www.j-walk.com/ss/excel/tips/tip63.htm



"Lori Switzer" wrote:

I need to make a workbook with 52 pages (1 for each week) that looks at the
previous page and updates accordingly. Right now, when I create a new page I
have been using find/replace to replace all page references to the correct
page. This is very time consuming and I am hoping to make it quicker as I
have to make 12 files this same way - one for each person.
Any suggestions will be greatly appreciated.

Gord Dibben MS Excel MVP