PDA

View Full Version : Solved: Skip Missing Worksheets



abba92
10-23-2007, 07:33 AM
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

xld
10-23-2007, 07:41 AM
Where does it do that? I get subscript out of range.

abba92
10-23-2007, 07:57 AM
It starts to look for an outside file when it's pasting the formulas and it doesn't find the worksheet name.

xld
10-23-2007, 08:43 AM
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
10-23-2007, 08:54 AM
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).

xld
10-23-2007, 09:33 AM
And do they refer to missing worksheets at all? If so just put



Application.DisplayAlerts = False


before the pastes and reset afterwards.

abba92
10-23-2007, 10:18 AM
Xld - It Worked! Thank you so much!