Coping from existing spreadsheet

[caldog]

I have a workbook that has each sheet linked to the prior sheet. Now when I
add a new sheet, I need it to only look back to the last sheet.

Sheet 1 is named ABC
Sheet 2 is named LKJ
Sheet 3 is named PUY

I need sheet 3 to look back to sheet 2, and sheet 2 to look back to sheet 1.

This workbook will have over a hundred sheets in it before I create a new
workbook.

Therefore, when I want to create a new sheet 4, the links are not refreshing
to show coming from sheet 3, instead they show they are coming from sheet 2.

Any help would be appreciated.

Steve

[Walt]

Hi Steve,

When you say "linked to the prior sheet" and "to the last
sheet", do you mean the worksheet immediately to the left of any
given worksheet? If so, what does the leftmost worksheet reference
and will new worksheets always be added to the far right position?
It's not clear how you determine just which worksheet is the prior
worksheet.

NOTE: If you click & drag the tab for worksheet 1 to the right of
worksheet 3 they will no longer be in number sequence. If that were
done, should worksheet 1 then reference worksheet 3?

I'll try to check back for your answers before pursuing this.

Best Regards,
Walt Weber

[caldog]

Hi Walt,
Yes to your question to the sheet directly to your left.

Say sheet 1 cell a5 has a date of 09/30/05
Then sheet 2 will show ='Sheet 1'!A5
Then sheet 3 cell a5 has been changed to 10/31/05
Then creating a new sheet 4 I want it to say ="Sheet3'!A5

But what I'm getting when creating new sheet 4 is the date 10/31/05, not
what I want. What I want is ='Sheet3'!A5

Or if I create sheets 5 through 10, they should all be referring back one
sheet.
Sheet 5 needs to look at sheet 4, and sheet 6 needs to look back at sheet 5
etc...

Hope this example clears it up for what I'm wanting.

Steve


"Walt" wrote:

> Hi Steve,
>
> When you say "linked to the prior sheet" and "to the last
> sheet", do you mean the worksheet immediately to the left of any
> given worksheet? If so, what does the leftmost worksheet reference
> and will new worksheets always be added to the far right position?
> It's not clear how you determine just which worksheet is the prior
> worksheet.
>
> NOTE: If you click & drag the tab for worksheet 1 to the right of
> worksheet 3 they will no longer be in number sequence. If that were
> done, should worksheet 1 then reference worksheet 3?
>
> I'll try to check back for your answers before pursuing this.
>
> Best Regards,
> Walt Weber
>
>

[Walt]

Hi Steve,

OK, that helps, but I still need to know (From my earlier post):

1) What does the leftmost worksheet reference?
2) Will new worksheets always be added to the far right position?
3) If you click & drag the tab for worksheet 1 to the right of
worksheet 3 they will no longer be in number sequence. If that were
done, should worksheet 1 then reference worksheet 3?

And, your second post leads me to ask:

4) Is there only the one cell date link between worksheets, or is
that just one of many formula links?
5) Please confirm or correct that you create the new worksheets via
the menus Edit/Move or copy Sheet.../(CkBox)Create a Copy.
6) Are all of the worksheets the same layout? For example, will any
given cell like B15 always have the same value or formula or usage as
that cell on any other worksheet?

I'm thinking of a possibility along the lines of:

(This would create a name in each worksheet, except the 1st, defining
the 'prior' worksheet)
Private Sub Workbook_NewSheet(ByVal Sh As Object)
For Each ws In ThisWorkbook.Worksheets
If ws.Index > 1 Then ThisWorkbook.Names.Add _
Name:="'" & ws.Name & "'!PriorSheet", RefersTo:= _
"'" & Worksheets(ws.Index - 1).Name & "'!"
Next
End Sub

And then maybe having you use the indirect function in the worksheet
like:
=INDIRECT(PriorSheet& ADDRESS(ROW(),COLUMN(),4))
where there are linked formulas. But, this wouldn't work if the
layout structure isn't rigid.

The NewSheet event trigger isn't perfect for this purpose because the
user might do other things like click & drag a worksheet tab which
would not trigger the update. I don't think there is an event that
would trigger by just moving a worksheet tab.

Depending on your answers to the questions above, this or some other
approach might be best - possibly an alternate approach would be much
simpler.

NOTE: Since you'll be adding worksheets, this VBA project will not
hold a digital signature.

Best Regards,
Walt Weber

[Walt]

Hi Steve,

An obvious question I meant to ask earlier is:

What operating system(s) and version(s) and what Excel version(s) will
this be expected to work with?

Best Regards,
Walt Weber

[caldog]

Walt,

Thanks for your reply, I was unavailable this weekend, so did not check for
responses until today.

Now on to your questions in section one and two:
1.The leftmost worksheet does not reference anything; it is the starting
point for all other worksheets that are created to the right.

2.Yes

3.All new worksheets added again will be to the right, and numerical order
Example: 1,2,3,4….

4.There were be many cells linking between say sheet one and sheet two, and
the same number and same cell location between sheet two and sheet three and
so on. This cells locations are fixed they will not be changed

5.I create a new sheet by right clicking on the last sheet in the workbook,
and selecting move/copy from the pop menu, and then always put new sheet at
end.

6.See four above

Working off of Excel 2002, Windows XP Professional Version 2002 with Service
Pack 2

Steve
"Walt" wrote:

> Hi Steve,
>
> An obvious question I meant to ask earlier is:
>
> What operating system(s) and version(s) and what Excel version(s) will
> this be expected to work with?
>
> Best Regards,
> Walt Weber
>
>

[Walt]

Hi Steve,

Within the very rigid structure and usage you've defined, I believe the
following will work for you:

1) Generally the supporting code will allow you to place
'INDIRECT(PriorSheet&ADDRESS(ROW(),COLUMN(),4))' in any formula of any
worksheet except the 1st on the left to reference the same cell on the
'prior' worksheet.
2) This part of the code, the event triggers, will go in the
'ThisWorkbook' code pane. These triggers, between them, are intended
to trap any instance where the user has inserted or moved worksheets
and to enforce the relationship you've defined.

Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call AlignAllPriors
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call InitializeFirstsheet
Call AlignAllPriors
End Sub

Private Sub Workbook_Open()
Dim NmExists As String, WS As Worksheet
'RESET FirstSheet DEFINITION IF ANY SHEETS FAIL
For Each WS In ThisWorkbook.Worksheets
On Error Resume Next
NmExists = WS.Names("FirstSheet")
On Error GoTo 0
If NmExists = "" Then
Call InitializeFirstsheet
Exit For
End If
NmExists = ""
Next
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call AlignAllPriors
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim FIRST As String
If Sh.Index = 1 Then
On Error Resume Next
FIRST = Sh.Names("FirstSheet")
On Error GoTo 0
If FIRST <> "=TRUE" Then Call AlignAllPriors
End If
End Sub

3) And this part will go in a code module pane. These routines do
the real work of maintaining the first sheet in its critical position
and adapting for any sheet movement and new worksheets. Two names,
'FirstSheet' and 'PriorSheet', are established and maintained in each
worksheet.

Sub AlignAllPriors()
Dim Prior As String, WS As Worksheet
On Error GoTo AlignAllPriorsERROR
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
For Each WS In ThisWorkbook.Worksheets
With WS 'ENFORCE FirstSheet POSITION
If .Names("FirstSheet") = "=TRUE" And .Index <> 1 Then
.Move Before:=Sheets(1)
End If
End With
Next
For Each WS In ThisWorkbook.Worksheets
With WS 'ENFORCE PRIOR SHEET RELATIONSHIP
If .Names("FirstSheet") = "=FALSE" Then
On Error Resume Next
Prior = .Names("PriorSheet").RefersTo
On Error GoTo 0
If Prior <> "=" & Chr(34) & "'" & _
Worksheets(.Index - 1).Name & "'!" & _
Chr(34) Then
ThisWorkbook.Names.Add _
Name:="'" & WS.Name & "'!PriorSheet", _
RefersTo:="'" & _
Worksheets(WS.Index - 1).Name & "'!"
End If
End If
End With
Next
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Exit Sub
AlignAllPriorsERROR:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
MsgBox "ERROR in AlignAllPriors routine"
End Sub

Sub InitializeFirstsheet()
Dim WS As Integer
On Error GoTo InitializeFirstsheetERROR
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ThisWorkbook.Names.Add Name:="'" & _
ThisWorkbook.Worksheets(1).Name & _
"'!FirstSheet", RefersTo:=True
If ThisWorkbook.Worksheets.Count > 1 Then
For WS = 2 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Names.Add Name:="'" & _
ThisWorkbook.Worksheets(WS).Name & _
"'!FirstSheet", RefersTo:=False
Next
End If
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Exit Sub
InitializeFirstsheetERROR:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
MsgBox "ERROR in InitializeFirstsheet routine"
End Sub

4) Please use a test copy of your model (NOT YOUR ORIGINAL). Copy
the code into the code panes as described above. Adjust for any word
wrapping that may have occurred in the code with this posting. Save,
close and reopen the model (This will serve to initialize the
worksheets' names).
5) This is an unusually rigid structure you've defined. Some of the
user actions that would make this solution fail are:
a. Inserting or deleting a column or row in any worksheet
b. Inserting a worksheet in any way but what you've defined.
c. Inserting anything but worksheets (ex. Chart Sheets, Dialog
Sheets, or XL 4.0 Macro Sheets)
6) I added the 'Workbook_SheetCalculate' event trap to be sure any
movement of the first sheet would be caught and its position enforced.
This adds to the calculation burden in the structure. Hopefully this
penalty is acceptable.

Please post back with any results.

Best Regards,
Walt Weber

[caldog]

Hi Walt thanks for the reply. I sure will post my results, but it my take me
a few days, because of what is going on at work.

Steve

"Walt" wrote:

> Hi Steve,
>
> Within the very rigid structure and usage you've defined, I believe the
> following will work for you:
>
> 1) Generally the supporting code will allow you to place
> 'INDIRECT(PriorSheet&ADDRESS(ROW(),COLUMN(),4))' in any formula of any
> worksheet except the 1st on the left to reference the same cell on the
> 'prior' worksheet.
> 2) This part of the code, the event triggers, will go in the
> 'ThisWorkbook' code pane. These triggers, between them, are intended
> to trap any instance where the user has inserted or moved worksheets
> and to enforce the relationship you've defined.
>
> Private Sub Workbook_BeforeSave _
> (ByVal SaveAsUI As Boolean, Cancel As Boolean)
> Call AlignAllPriors
> End Sub
>
> Private Sub Workbook_NewSheet(ByVal Sh As Object)
> Call InitializeFirstsheet
> Call AlignAllPriors
> End Sub
>
> Private Sub Workbook_Open()
> Dim NmExists As String, WS As Worksheet
> 'RESET FirstSheet DEFINITION IF ANY SHEETS FAIL
> For Each WS In ThisWorkbook.Worksheets
> On Error Resume Next
> NmExists = WS.Names("FirstSheet")
> On Error GoTo 0
> If NmExists = "" Then
> Call InitializeFirstsheet
> Exit For
> End If
> NmExists = ""
> Next
> End Sub
>
> Private Sub Workbook_SheetActivate(ByVal Sh As Object)
> Call AlignAllPriors
> End Sub
>
> Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
> Dim FIRST As String
> If Sh.Index = 1 Then
> On Error Resume Next
> FIRST = Sh.Names("FirstSheet")
> On Error GoTo 0
> If FIRST <> "=TRUE" Then Call AlignAllPriors
> End If
> End Sub
>
> 3) And this part will go in a code module pane. These routines do
> the real work of maintaining the first sheet in its critical position
> and adapting for any sheet movement and new worksheets. Two names,
> 'FirstSheet' and 'PriorSheet', are established and maintained in each
> worksheet.
>
> Sub AlignAllPriors()
> Dim Prior As String, WS As Worksheet
> On Error GoTo AlignAllPriorsERROR
> Application.Calculation = xlCalculationManual
> Application.EnableEvents = False
> For Each WS In ThisWorkbook.Worksheets
> With WS 'ENFORCE FirstSheet POSITION
> If .Names("FirstSheet") = "=TRUE" And .Index <> 1 Then
> .Move Before:=Sheets(1)
> End If
> End With
> Next
> For Each WS In ThisWorkbook.Worksheets
> With WS 'ENFORCE PRIOR SHEET RELATIONSHIP
> If .Names("FirstSheet") = "=FALSE" Then
> On Error Resume Next
> Prior = .Names("PriorSheet").RefersTo
> On Error GoTo 0
> If Prior <> "=" & Chr(34) & "'" & _
> Worksheets(.Index - 1).Name & "'!" & _
> Chr(34) Then
> ThisWorkbook.Names.Add _
> Name:="'" & WS.Name & "'!PriorSheet", _
> RefersTo:="'" & _
> Worksheets(WS.Index - 1).Name & "'!"
> End If
> End If
> End With
> Next
> Application.Calculation = xlCalculationAutomatic
> Application.EnableEvents = True
> Exit Sub
> AlignAllPriorsERROR:
> Application.Calculation = xlCalculationAutomatic
> Application.EnableEvents = True
> MsgBox "ERROR in AlignAllPriors routine"
> End Sub
>
> Sub InitializeFirstsheet()
> Dim WS As Integer
> On Error GoTo InitializeFirstsheetERROR
> Application.Calculation = xlCalculationManual
> Application.EnableEvents = False
> ThisWorkbook.Names.Add Name:="'" & _
> ThisWorkbook.Worksheets(1).Name & _
> "'!FirstSheet", RefersTo:=True
> If ThisWorkbook.Worksheets.Count > 1 Then
> For WS = 2 To ThisWorkbook.Worksheets.Count
> ThisWorkbook.Names.Add Name:="'" & _
> ThisWorkbook.Worksheets(WS).Name & _
> "'!FirstSheet", RefersTo:=False
> Next
> End If
> Application.Calculation = xlCalculationAutomatic
> Application.EnableEvents = True
> Exit Sub
> InitializeFirstsheetERROR:
> Application.Calculation = xlCalculationAutomatic
> Application.EnableEvents = True
> MsgBox "ERROR in InitializeFirstsheet routine"
> End Sub
>
> 4) Please use a test copy of your model (NOT YOUR ORIGINAL). Copy
> the code into the code panes as described above. Adjust for any word
> wrapping that may have occurred in the code with this posting. Save,
> close and reopen the model (This will serve to initialize the
> worksheets' names).
> 5) This is an unusually rigid structure you've defined. Some of the
> user actions that would make this solution fail are:
> a. Inserting or deleting a column or row in any worksheet
> b. Inserting a worksheet in any way but what you've defined.
> c. Inserting anything but worksheets (ex. Chart Sheets, Dialog
> Sheets, or XL 4.0 Macro Sheets)
> 6) I added the 'Workbook_SheetCalculate' event trap to be sure any
> movement of the first sheet would be caught and its position enforced.
> This adds to the calculation burden in the structure. Hopefully this
> penalty is acceptable.
>
> Please post back with any results.
>
> Best Regards,
> Walt Weber
>
>