I am using excel 2000 and have a workbook with 2 sheets, Targets and
Individual Store Targets

The Target Sheet has the the Store Name in column A, and various
targets in columns B, C, D & E, the data starts in Row 4 and currently
goes down to row 81 but is constantly increasing

On the Individual Store Targets sheet I have links to the data on the
Targets sheet and I wrote a macro to change the links to each stores
name and targets and print it,my code is below:

[color="Red"]Sub PrintIndividualStoreTargets()
'
' PrintIndividualStoreTargets Macro
' Macro recorded 19/12/2005 by
'

'
Sheets("Individual Store Targets").Visible = True
Sheets("Individual Store Targets").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A3:D3,A6,B6,C6,D6").Select
Range("D6").Activate
Selection.Replace What:="4", Replacement:="5", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.Replace What:="5", Replacement:="6", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.Replace What:="6", Replacement:="7", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

etc down to 81 and finishes with:

Selection.Replace What:="81", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A2").Select
Sheets("Individual Store Targets").Visible = False

Is there any way this code could be shortened and written in a way that
each time I add a new store it automatically picks it up instead of
having to add the the edit replace for the new store

Thanks in advance


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=554863