Consulting

Results 1 to 2 of 2

Thread: Copy mode is giving error

  1. #1
    VBAX Mentor jammer6_9's Avatar
    Joined
    Apr 2007
    Location
    Saudi Arabia
    Posts
    318
    Location

    Copy mode is giving error

    After successfuly creating a number of sheets, code stop and giving "Copy Mode" error on the line (Red Color)? Any idea why?

    [VBA]
    Sub MakeNewSheets()


    MsgBox ("This will take few minutes, do not interrupt the process"), vbInformation, "ofsjcr"

    Application.ScreenUpdating = False

    Application.DisplayStatusBar = True

    Application.StatusBar = "Please wait while performing task, do not interrupt the process"

    Worksheets("Master").Visible = xlSheetVisible

    'Application.ScreenUpdating = False

    Sheets("MENU ITEMS SUM ").Activate

    For Each Cell In Sheets("MENU ITEMS SUM ").Range("J3", Range("J368").End(xlUp))

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''

    Sheets("Master").Select
    Range("P15:Q43").Select
    Selection.ClearContents


    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''

    Sheets("Recipe_update").Select
    ActiveSheet.Range("$O$5:$O$6000").AutoFilter Field:=14, Criteria1:=Cell _
    , Operator:=xlAnd

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''

    With Range("S:T")
    .Find(what:="*", after:=.Cells(1, 1), LookIn:=xlValues).Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    End With

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''

    Sheets("Master").Select
    Range("P15:Q43").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Range("A1").Select


    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''

    If Not SheetExists(Cell.Value) Then
    Sheets("Master").Copy after:=Sheets(Sheets.Count)
    With ActiveSheet
    .Name = Cell
    .Range("B1") = Cell
    End With
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    End If


    Sheets("MENU ITEMS SUM ").Activate


    Next
    Worksheets("Master").Visible = xlSheetVeryHidden
    ' Application.ScreenUpdating = True
    Application.StatusBar = False
    Application.ScreenUpdating = True

    End Sub
    [/VBA]
    T-ogether
    E-veryone
    A-chieves
    M-ore


    One who asks a question is a fool for five minutes; one who does not ask a question remains a fool forever.

  2. #2
    VBAX Regular
    Joined
    Oct 2010
    Posts
    73
    Location
    jammer6_9,

    When I looked at your code I receive an error on

    [vba]

    If Not SheetExists(Cell.Value) Then

    [/vba]

    In your code I did not see where you tried to find the "Cell.Value"
    My Time is valuable. The forum's time is valuable.
    So take time to say "Thanks"

    As with all programs there may be an easier way!
    CharlesH

Posting Permissions

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