Don't go to page! Screenupdating=False

mssbass

Board Regular
Joined
Nov 14, 2002
Messages
163
Platform
Windows
Can anyone help rewrite this code to prevent Excel jumping to the worksheet: ZMROSALES MAP? :confused:

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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

mssbass

Board Regular
Joined
Nov 14, 2002
Messages
163
Platform
Windows
I want the code to run in the background while on the Main worksheet without jumping to the ZMROSALES MAP worksheet.
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,269
Office Version
365
Platform
Windows
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:

mssbass

Board Regular
Joined
Nov 14, 2002
Messages
163
Platform
Windows
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.
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,269
Office Version
365
Platform
Windows
My code should do that - it just references the other sheets without moving from the main sheet.
 

mssbass

Board Regular
Joined
Nov 14, 2002
Messages
163
Platform
Windows
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
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,269
Office Version
365
Platform
Windows
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

Board Regular
Joined
Nov 14, 2002
Messages
163
Platform
Windows
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)?
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,269
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,806
Messages
5,446,568
Members
405,408
Latest member
Bravomb96

This Week's Hot Topics

Top