VBA - De-select pasted area

[mnyankee]

In the code below I am copying and pasting data from my pivot table into two other sheets. At the end of the process the pasted area remains selected on both sheets. How do I remove that selection and just have the cursor in say cell A1 or wherever I want to leave it on those two sheets? I tried Sheets("Current Report").Range("A1").Select but that results in an error. Surely it is something simple I am overlooking.

Sub CopyPivotTableData()

Application.ScreenUpdating = False 'speeds up the process by not refreshing the screen
Application.CutCopyMode = False 'added to keep clipboard cache empty

Sheets("Current Report").Activate
Sheets("Current Report").Range("A8:S50").ClearContents
Sheets("Proposed Report").Activate
Sheets("Proposed Report").Range("A10:S50").ClearContents

Sheets("Pivot Table").Activate
Sheets("Pivot Table").Range("A5:S5", Range("A" & Rows.Count).End(xlUp).Offset(-1, 0)).Copy

Sheets("Current Report").Range("A8").PasteSpecial xlPasteValues
Sheets("Proposed Report").Range("A10").PasteSpecial xlPasteValues

'Sheets("Current Report").Range("A1").Select
'Sheets("Proposed Report").Range("A1").Select

Application.ScreenUpdating = True 'turns refreshing back on
Application.CutCopyMode = True 'turns clipboard cache use back on


End Sub

 

[WaywardWes]

I think adding another "Application.CutCopyMode = False" at the end should turn it off.

 

[AlphaFrog]

"Sheets("Current Report").Range("A1").Select"
You would get an error with this line of code if Current Report is not the active sheet... which it is not in your example code. First select the sheet. Then select the cell..

Here is an alternative method without using PasteSpecial and changing the range selections.

Sub CopyPivotTableData()
   
    Dim rngCopy As Range
   
    Application.ScreenUpdating = False 'speeds up the process by not refreshing the screen
   
    With Sheets("Pivot Table")
        Set rngCopy = .Range("A5:S5", .Range("A" & Rows.Count).End(xlUp).Offset(-1, 0))
    End With
   
    Sheets("Current Report").Range("A8:S50").ClearContents
    Sheets("Current Report").Range("A8").Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
   
    Sheets("Proposed Report").Range("A10:S50").ClearContents
    Sheets("Proposed Report").Range("A10").Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
   
    Application.ScreenUpdating = True 'turns refreshing back on
   
End Sub

 

[mnyankee]

"Sheets("Current Report").Range("A1").Select"
You would get an error with this line of code if Current Report is not the active sheet... which it is not in your example code. First select the sheet. Then select the cell..

Here is an alternative method without using PasteSpecial and changing the range selections.

Sub CopyPivotTableData()
  
    Dim rngCopy As Range
  
    Application.ScreenUpdating = False 'speeds up the process by not refreshing the screen
  
    With Sheets("Pivot Table")
        Set rngCopy = .Range("A5:S5", .Range("A" & Rows.Count).End(xlUp).Offset(-1, 0))
    End With
  
    Sheets("Current Report").Range("A8:S50").ClearContents
    Sheets("Current Report").Range("A8").Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
  
    Sheets("Proposed Report").Range("A10:S50").ClearContents
    Sheets("Proposed Report").Range("A10").Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
  
    Application.ScreenUpdating = True 'turns refreshing back on
  
End Sub

Thanks AlphaFrog. I am adopting your solution and appreciate the guidance.