Hi - am currently using Excel 2003
I really appreciate any help on this. I am using a master worksheet (new) to
create and copy to other sheets and am using macro to auto update tab name
from cell A2 - this works great.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
ActiveSheet.Name = Range("a2").Value
End Sub
The problem is that I have another macro that I use to copy information
from the copied worksheets to and from another worksheet (budget). When the
master is copied, the reference remains at 'new'. I have tried just using
Sheet2 as a reference but this won't work as I need to create lots of copied
worksheets (therfore the worksheets will be constantly moving) and I will
need to use the budget macro in each worksheet at different times. Here is
what I currently have (created using recording macros - I really am a VBA
amateur).
Sub gotocalc()
'
' gotocalc Macro
' Macro recorded by
'

'
If Range("m24").Value < "Required" _
Then
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Sheets("calc").Visible = True
Sheets("Calc").Select
ActiveSheet.Unprotect
Sheet2.Select
Range("c5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Calc").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheet2.Select
Range("I5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Calc").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheet2.Select
ActiveWindow.SmallScroll Down:=-6
Range("D18:E18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Calc").Select
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheet2.Select
Range("D20").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Calc").Select
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheet2.Select
Range("q23").Select
ActiveSheet.Protect
Sheets("Calc").Select
ActiveSheet.Protect
Else
MsgBox "Budget has not been received from service - budget calculator
cannot be created. If budget has been received enter date"
Range("m24").Select
ActiveSheet.Protect
End If

End Sub

I hope this makes some sense. Thanks heaps in anticipation
Janelle