relative sheet referencing - what's the secret?

[Glennster]

I'm stumped!!

In Excel 2000:

One workbook, starting with 2 "master" sheets (used as templates for
additional sheets)


TEMPlocation
TEMPsummary (with formulas that reference TEMPlocation!)

These sheets are normally hidden.

Upon user opening for for the first time:

I have a macro that allows user to input 4 char "location" code, then
the macro copies and renames two NEW sheets (concatenating the code)
as follows:

locCode = inputbox...
Sheets("TEMPlocation").Visible = True
Sheets("TEMPlocation").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = locCode & "location"

Sheets("TEMPsummary").Visible = True
Sheets("TEMPsummary").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = locCode & "summary"

Upon reviewing the new location sheets, I notice the formulas in
LOC1summary still reference the original "TEMPlocation" sheet instead
of the "LOC1location" sheet.

I understand if I RENAME the sheets, Excel will update the formulas,
but I'll lose my template files for future additions.

I've attepted many different scenarios of copying, then renaming but I
can't seem to save the original sheets, and make new sheets that Excel
will update references to each other.

My closest solution has been by "hardcoding a reference" by creating a
named range (cell) that stores the text location code, then reference
that cell using the INDIRECT function to create the formula sheet
reference (as follows):

Cell A1 (value): LOC1

Formula cell B2 on same sheet: =INDIRECT(TRIM($A$1) & "location!L73")

resulting in (formula): =LOC1location!L73

This works - sort of....

The concern here is that the cell reference on the target sheet is
absolute, and if rows/columns are added/deleted this will kill the
formula.

Is there a way to make the target cell reference (which is on a
different sheet) relative?

OR

Is there a better way to copy / rename the sheets originally so that
Excel simply updates the formula references?

Thanks in advance for you consideration!

Regards,
Glenn

 

[Guest]

The secret is not obvious, but it is simple: select the two sheets together
and copy them at the same time:

Public Sub CopyTogether(S1 As Worksheet, S2 As Worksheet)
Dim C As Integer
C = Worksheets.Count
Sheets(Array(S1.Name, S2.Name)).Copy After:=Sheets(C)
End Sub