Solved: Skip Missing Worksheets

[abba92]

Hello ~

I have a workbook that can have a number of worksheets from 1 to 12. I want to create a macro that will populate the existing worksheets with formulas from a Master sheet if the worksheet has the name "Style XX Key" (where XX is a number between 1 and 12). The code is listed below, but instead of skipping the non-existent worksheets, it's opening a "file open" dialog box for me to select the appropriate file.

Any help would be great.

Code:

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

[Bob Phillips]

Where does it do that? I get subscript out of range.

[abba92]

It starts to look for an outside file when it's pasting the formulas and it doesn't find the worksheet name.

[Bob Phillips]

Am I missing something, I don't see any formulae?

is it in the ACtivesheet.Paste? What exactly are you trying to do? Does the worksheet already have formulae referring to missing sheets?

[abba92]

Yes. Style 1 Key has the formulas in it. I'm trying to copy those formulas from Style 1 Key into the remaining Style XX Key pages (the number of which changes based on how many the user created).

[Bob Phillips]

And do they refer to missing worksheets at all? If so just put



Application.DisplayAlerts = False


before the pastes and reset afterwards.

[abba92]

Xld - It Worked! Thank you so much!