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
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