Consulting

Results 1 to 7 of 7

Thread: Solved: Skip Missing Worksheets

  1. #1
    VBAX Regular
    Joined
    Nov 2006
    Location
    Seattle
    Posts
    12
    Location

    Thumbs down Solved: Skip Missing Worksheets

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    Where does it do that? I get subscript out of range.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Nov 2006
    Location
    Seattle
    Posts
    12
    Location
    It starts to look for an outside file when it's pasting the formulas and it doesn't find the worksheet name.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Nov 2006
    Location
    Seattle
    Posts
    12
    Location
    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).

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    And do they refer to missing worksheets at all? If so just put

    [vba]

    Application.DisplayAlerts = False
    [/vba]

    before the pastes and reset afterwards.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Regular
    Joined
    Nov 2006
    Location
    Seattle
    Posts
    12
    Location
    Xld - It Worked! Thank you so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •