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
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