Don't go to page! Screenupdating=False

mssbass

Active Member
Joined
Nov 14, 2002
Messages
253
Platform
  1. 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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
I want the code to run in the background while on the Main worksheet without jumping to the ZMROSALES MAP worksheet.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
My code should do that - it just references the other sheets without moving from the main sheet.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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)?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top