Results 1 to 10 of 10
Like Tree1Likes
  • 1 Post By Darren Bartrup

Don't go to page! Screenupdating=False

This is a discussion on Don't go to page! Screenupdating=False within the Excel Questions forums, part of the Question Forums category; Can anyone help rewrite this code to prevent Excel jumping to the worksheet: ZMROSALES MAP? Sub CopyAppend() Dim oldWS As ...

  1. #1
    Board Regular
    Join Date
    Nov 2002
    Location
    Elberta, AL
    Posts
    125

    Default Don't go to page! Screenupdating=False

    Can anyone help rewrite this code to prevent Excel jumping to the worksheet: ZMROSALES MAP?

    Sub CopyAppend()
    Dim oldWS As Worksheet
    Set oldWS = ActiveSheet
    Application.ScreenUpdating = False
    Application.Goto (ActiveWorkbook.Sheets("ZMROSALES MAP").Range("a1"))
    ActiveSheet.PivotTables("PivotTodaysOpenHolds2").PivotSelect "Notification[All]", _
    xlLabelOnly, True
    Selection.Copy
    Application.Goto (ActiveWorkbook.Sheets("NotifTasks").Range("b3"))
    Selection.End(xlDown).Select

    intNewRow = Application.ActiveCell.Row + 1
    strNewCell = "b" & intNewRow
    Application.Range(strNewCell).Activate
    ActiveSheet.Paste
    oldWS.Activate

    End Sub

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    5,991

    Default Re: Don't go to page! Screenupdating=False

    So what do you want it do instead?

  3. #3
    Board Regular
    Join Date
    Nov 2002
    Location
    Elberta, AL
    Posts
    125

    Default Re: Don't go to page! Screenupdating=False

    I want the code to run in the background while on the Main worksheet without jumping to the ZMROSALES MAP worksheet.

  4. #4
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,253

    Default Re: Don't go to page! Screenupdating=False

    From what I can see in your code you want it to:
    • Copy a part of the pivot table called 'PivotTodaysOpenHolds2'
    • Paste it to the bottom of column B on the NotIfTasks sheet.
    • Return to whichever sheet was active when you started.


    Code:
    Sub CopyAppend()
    
        Dim pt As PivotTable
        Dim rLastCell As Range
        
        ''''''''''''''''''''''''''''
        'Reference to pivot table. '
        ''''''''''''''''''''''''''''
        Set pt = ThisWorkbook.Worksheets("ZMROSALES MAP").PivotTables("PivotTodaysOpenHolds2")
        
        '''''''''''''''''''''''''''''''''''''''''
        'Find last cell on NotifTasks in col B. '
        '''''''''''''''''''''''''''''''''''''''''
        Set rLastCell = ThisWorkbook.Worksheets("NotifTasks").Cells(Rows.Count, 2).End(xlUp)
        
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'Copy the pivot table to 1 cell below the last cell. '
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''
        pt.RowRange.Copy Destination:=rLastCell.Offset(1)
        
    End Sub
    I'm not sure what part of the pivot table you're copying - this link will tell you how to reference different parts.
    Referencing Pivot Table Ranges in VBA - Peltier Tech Blog

    Edit:
    Your code is referencing cell B500 this way:
    Code:
    Sub test()
    
        Dim strNewCell As String
        Dim intNewRow As Integer
        intNewRow = 500
        
        strNewCell = "b" & intNewRow
        Application.Range(strNewCell).Activate
    
    
    End Sub
    This might be easier:
    Code:
    Sub test1()
    
    
        Dim strNewCell As String
        Dim intNewRow As Integer
        intNewRow = 500
        
        Cells(intNewRow, 2).Activate
    
    
    End Sub
    Cells is the single cell relative of Range and can be referenced using both row and column numbers.
    e.g. Cells(row_number, column_number).Activate
    Last edited by Darren Bartrup; Apr 29th, 2015 at 11:01 AM.
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  5. #5
    Board Regular
    Join Date
    Nov 2002
    Location
    Elberta, AL
    Posts
    125

    Default Re: Don't go to page! Screenupdating=False

    yes - you're correct - I'm copying column A from the pivot table and pasting it to the bottom of column B on the NotifTasks worksheet. However, I don't want the user of the document to see this happening. They should just being staying on the "Main" worksheet while this code runs in the background.

  6. #6
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,253

    Default Re: Don't go to page! Screenupdating=False

    My code should do that - it just references the other sheets without moving from the main sheet.
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  7. #7
    Board Regular
    Join Date
    Nov 2002
    Location
    Elberta, AL
    Posts
    125

    Default Re: Don't go to page! Screenupdating=False

    Darren... The code is placing 2 rows of data at the bottom. It should just place 1 row of data.

    Sub CopyAppend()
    Dim pt As PivotTable
    Dim rLastCell As Range

    ''''''''''''''''''''''''''''
    'Reference to pivot table. '
    ''''''''''''''''''''''''''''
    Set pt = ThisWorkbook.Worksheets("ZMROSALES MAP").PivotTables("PivotTodaysOpenHolds2")

    '''''''''''''''''''''''''''''''''''''''''
    'Find last cell on NotifTasks in col B. '
    '''''''''''''''''''''''''''''''''''''''''
    Set rLastCell = ThisWorkbook.Worksheets("NotifTasks").Cells(Rows.Count, 2).End(xlUp)

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Copy the pivot table to 1 cell below the last cell. '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    pt.RowRange.Copy Destination:=rLastCell.Offset(1)

    End Sub

  8. #8
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,253

    Default Re: Don't go to page! Screenupdating=False

    RowRange on the last row of my code copies the labels from the pivot table - including the header and grand total (if present) - so I'm guessing your pivot table has a couple of rows?

    To exclude the header and grand total change pt.RowRange.Copy to pt.RowRange.Offset(1).Resize(pt.RowRange.Rows.Count-2).Copy
    To exclude just the header use -1 instead of -2.
    (I'm sure there's better ways of doing this - this is just the way I know).
    mssbass likes this.
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  9. #9
    Board Regular
    Join Date
    Nov 2002
    Location
    Elberta, AL
    Posts
    125

    Default Re: Don't go to page! Screenupdating=False

    You're awesome!!! - Can you also tell me how to place a 0 in column J of the NotifTasks (same new row as this pasted in)?

  10. #10
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,253

    Default Re: Don't go to page! Screenupdating=False

    Code:
    rLastCell.Offset(1)
    The above code returns a reference to the first empty cell in column B. You're after the same reference row, but on column J which is 8 columns to the right. Place this code anywhere after rLastCell has been set:

    Code:
    rLastCell.Offset(1,8) = 0
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com