Worksheet_Change Error with Pivot Table Refresh on diff sheet

[samjessek]

Hello, I am very new to VBA and any help would be appreciated.

The purpose of this code is to reformat one pivot table upon a change on the same worksheet only. I have placed the code in the individual worksheet object. It works perfectly EXCEPT when I refresh a pivot table on a separate sheet I receive a ‘Run-time Error ‘1004’ Select Method of Range class Failed message.

The line that is highlighted in the debugger is after the interest line “Range("D9:L9").Select”. There will be very light VBA in this workbook and any ugly fix will work. Even something similar to an “IFERROR” that simply exits the sub. I’m confused as to why the sub is even activated if I am on a separate worksheet. Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)    Application.ScreenUpdating = False
    Dim Rng As Range
    Set Rng = Intersect(Target, Range("A10:M500"))
        Range("D9:L9").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
        Range("M9").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("D:L").Select
    Selection.ColumnWidth = 16
    Range("A1").Select
End Sub

 

[GRTorres]

where do you have this code placed at? Which Object Sheet? Or a module?

 

[samjessek]

Object sheet. 'Sheet4(Personnel)'

 

[GRTorres]

Try this to correct:

Private Sub Worksheet_Change(ByVal Target As Range)  Application.ScreenUpdating  = False

Dim ws As Object
Dim Rng As Range

If ws.Name <> "Personnel" Then Exit Sub

'place code here

End Sub

 

[GRTorres]

Correction, place in the ThisWorkbook Module:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "Personnel" Then Exit Sub
'
'rest of code
'
End Sub

 

[samjessek]

It worked at preventing the refresh error, but the change event was no longer triggered for my originally targeted sheet.

 

[GRTorres]

Set your ranges as Sh.Range