Update Formulas on Consecutive Sheets in a Range
 

When I add sheets, I have copy a base sheet (P1) and paste it to a new sheet.
I have a macro that copies the row and column properties and then applies it
to the new sheets.
What I want to do now is run a macro to change the formulas. A simple
replace command works, but I need code that will advance by one for each new
sheet. I recorded the macro below to give you a feel of what I am trying to
do. I have sheets P1 to P30, but could have more P? sheets, up to 100. All
the sheets will have the same formulas, just 1 more than the previous sheet.
You can assume always running the macro from the P1 sheet. The initial
formula always starts with column 17 and each each thereafter must be +1,
then +2, etc.
Thanks in advance!

Sub UpdateFormulas()
Sheets("P3").Select
Range("C15:I15").Select
Selection.Replace What:="17", Replacement:="19", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C24:I24").Select
Selection.Replace What:="17", Replacement:="19", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("P4").Select
Range("C15:I15").Select
Selection.Replace What:="17", Replacement:="20", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C24:I24").Select
Selection.Replace What:="17", Replacement:="20", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("P5").Select
End Sub

Update Formulas on Consecutive Sheets in a Range
 

David, I haven't tried this but:
Sub UpdateFormulas()
Dim iSheet%, sSheet$, sNum$
For iSheet = 2 To 100
sSheet = Format$(iSheet, "###")
sNum = Format$(iSheet+16, "###")
On Error Goto UFZ1
Sheets(sSheet).Select
On Error Goto 0
Range("C15:I15").Select
Selection.Replace What:="17", Replacement:=sNum, LookAt:=xlPart, _
etc.
Next iSheet
UFZ1:
End Sub ' David2

"David" wrote:
When I add sheets, I have copy a base sheet (P1) and paste it to a new sheet.
I have a macro that copies the row and column properties and then applies it
to the new sheets.
What I want to do now is run a macro to change the formulas. A simple
replace command works, but I need code that will advance by one for each new
sheet. I recorded the macro below to give you a feel of what I am trying to
do. I have sheets P1 to P30, but could have more P? sheets, up to 100. All
the sheets will have the same formulas, just 1 more than the previous sheet.
You can assume always running the macro from the P1 sheet. The initial
formula always starts with column 17 and each each thereafter must be +1,
then +2, etc.
Thanks in advance!

Sub UpdateFormulas()
Sheets("P3").Select
Range("C15:I15").Select
Selection.Replace What:="17", Replacement:="19", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C24:I24").Select
Selection.Replace What:="17", Replacement:="19", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("P4").Select
Range("C15:I15").Select
Selection.Replace What:="17", Replacement:="20", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C24:I24").Select
Selection.Replace What:="17", Replacement:="20", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("P5").Select
End Sub


----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----