Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro - Worksheet Deactivate (Excel 2002)

    Hi,
    The code below doesn't like the Column("R:R").Select code... of course the macro works fine if its just a regular macro and NOT a private sub with the deactivation option... can anyone tell me why please?
    Thanks!
    Lana

    Private Sub Worksheet_Deactivate()

    'Copy & paste item description to new column so it can be used
    'in both the page & row sections of the pivot table
    With Sheet3
    Columns("R:R").Select
    Selection.Copy
    Range("AP1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Columns("AP:AP").EntireColumn.AutoFit
    Range("AP2").Select
    ActiveCell.FormulaR1C1 = "Item Description "
    End With

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro - Worksheet Deactivate (Excel 2002)

    It fails precisely because you're deactivating the sheet - you can't select cells on a sheet that's being deactivated. Fortunately, it's possible to avoid selecting cells.
    Moreover, although you use With ... End With, you don't do anything with it.
    And if this code is for the sheet Sheet3, you don't have to name it at all - in the worksheet module you can refer to the sheet as Me.
    Here is a modified version of the code:
    <code>
    Private Sub Worksheet_Deactivate()
    Application.EnableEvents = False
    'Copy & paste item description to new column so it can be used
    'in both the page & row sections of the pivot table
    With Me
    .Columns("R:R").Copy
    .Range("AP1").PasteSpecial Paste:=xlPasteValues
    .Columns("AP:AP").EntireColumn.AutoFit
    .Range("AP2") = "Item Description "
    End With
    Application.CutCopyMode = False
    Application.EnableEvents = True
    End Sub
    </code>
    The . before Columns and Range indicates that they belong to the item specified in With ... End With. In fact, still assuming that the code is for Sheet3, you don't need the With ... End With at all, since ranges automatically refer to the sheet unless otherwise specified:
    <code>
    Private Sub Worksheet_Deactivate()
    Application.EnableEvents = False
    'Copy & paste item description to new column so it can be used
    'in both the page & row sections of the pivot table
    Columns("R:R").Copy
    Range("AP1").PasteSpecial Paste:=xlPasteValues
    Columns("AP:AP").EntireColumn.AutoFit
    Range("AP2") = "Item Description "
    Application.CutCopyMode = False
    Application.EnableEvents = True
    End Sub</code>

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro - Worksheet Deactivate (Excel 2002)

    Ahh... I knew it had something to do with the "deactivation" option. The With and End With I threw in there just to see if it'd work (a hail Mary kind of thing, as I'm really green with this stuff still... as you know!)
    Thanks for the code corrections, and especially for explaining WHY!
    I really appreciate the your patience!
    Lana

Posting Permissions

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