Copy and Paste not working after running VBA code

dandelion

New Member
Joined
Jul 16, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have a pivot table in from D10 to L(lastrow) and a range (AA:AH) that will copy and paste a formula from Rows 9 to Rows 11 onward depending the number of last rows in pivot table.
However, There are 02 problem occuring when I run this code:
1. Copy and Paste stop working in any cells of the sheet
2. If I want to hide / group Rows 9 and 10, every rows from AH to AG are all deleted if not available value are selected from pivot (or lastrow of pivottable is smaller than last row of range AA:AH)

Please help. Thanks a lot

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws As Worksheet
Dim l_rPivot As Double
Dim l_rFormula As Double

Set ws = ActiveSheet

l_rPivot = ws.Cells(Rows.Count, "D").End(xlUp).Row
l_rFormula = ws.Cells(Rows.Count, "AD").End(xlUp).Row

If l_rPivot > l_rFormula Then
ws.Range("AA9:AH9").Copy
ws.Range(Cells(l_rFormula + 1, "AA"), Cells(l_rPivot, "AH")).PasteSpecial xlPasteAll

ElseIf l_rPivot < l_rFormula Then
ws.Range(Cells(l_rPivot + 1, "AA"), Cells(l_rFormula, "AH")).Delete Shift:=xlUp

End If

Application.CutCopyMode = False

End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,214,965
Messages
6,122,495
Members
449,088
Latest member
Melvetica

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