Skip or Ignore Missing Worksheets

[abba92]

I have an Excel workbook that allows a user to select the number of
worksheets they want to create (it's a makeshift Purchase Order - one
worksheet equals one style).

Because there can be a large number of worksheets, I've only put the
formulas in the first (pre-existing) worksheet. I want to create a
"calculate entire Purchase Order" macro that copies the formulas from
the first (pre-existing) worksheet and pastes it into however many
worksheets that have the name "Style XXX Key". The 'XXX' is the
variable that can be anywhere from 1 to 12.

Here's the code I have (I have the sheets hidden until they click the
"Calculate" button:

Sub CalcPO()
'
' CalcPO Macro
' Macro recorded 10/22/2007 by xyou
'

'
On Error Resume Next
Sheets("Style 1 Key").Visible = True
Sheets("Style 2 Key").Visible = True
Sheets("Style 3 Key").Visible = True
Sheets("Style 4 Key").Visible = True
Sheets("Style 5 Key").Visible = True
Sheets("Style 6 Key").Visible = True
Sheets("Style 7 Key").Visible = True
Sheets("Style 8 Key").Visible = True
Sheets("Style 9 Key").Visible = True
Sheets("Style 10 Key").Visible = True
Sheets("Style 11 Key").Visible = True
Sheets("Style 12 Key").Visible = True
On Error GoTo 0
Sheets("Style 1 Key").Select
Cells.Select
Selection.Copy
Sheets("Style 2 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 2",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
On Error Resume Next
Sheets("Style 3 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 3",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 4 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 4",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 5 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 5",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 6 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 6",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 7 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 7",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 8 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 8",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 9 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 9",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 10 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 10",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 11 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 11",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 12 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 12",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
On Error GoTo 0
Sheets("Style 1 Key").Select
Application.CutCopyMode = False
Range("B1").Select
End Sub


It works great until it gets to the "Style XXX Key" worksheet that
doesn't exists - then it prompts me to select a file from my PC.
Yikes!

Any help would be most appreciated.

 

[Bernie Deitrick]

Yikes! is right ;-)

Try the version below.

HTH,
Bernie
MS Excel MVP

Sub CalcPOVer2()
Dim i As Integer
Dim myF As Range
Dim myA As Range
Dim mySht As Worksheet

On Error Resume Next

For i = 1 To 12
Sheets("Style " & i & " Key").Visible = True
Next i

Set myF = Sheets("Style 1 Key").Cells.SpecialCells(xlCellTypeFormulas)

For i = 2 To 12
On Error GoTo NotFound:
Set mySht = Sheets("Style " & i & " Key")
For Each myA In myF.Areas
myA.Copy mySht.Range(myA.Address)
Next myA
mySht.Cells.Replace What:="Style 1", Replacement:="Style " & i, LookAt:=xlPart
NotFound:
Resume StartAgain
StartAgain:
Next i

Application.CutCopyMode = False
End Sub