Workbook_SheetChange event

[ajm]

I need pivot tables to refresh automatically once a user enters new source data. I have my workbook set up on monthly tabs and the users can enter data below row 12 on each of these tabs.

I have set up the workbook with dashboard/display tabs at the front and the monthly data sheets at the rear, bookended by two sheets "First" and "Last". I have attempted to repurpose some old code in an attempt to refresh all pivot tables when any row below row 12, between columns A and Z is changed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'///refreshes all pivot tables when sheets changed.
    Dim rng As Range
    Set rng = Target.Parent.Range("a12:z500")


    Dim shtFirst%, shtLast%
    shtFirst = Worksheets("First").Index
    shtLast = Worksheets("Last").Index
    '    MsgBox ActiveSheet.Name
    If ActiveSheet.Index <= shtFirst Then Exit Sub
    If ActiveSheet.Index >= shtLast Then Exit Sub


    'If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    
 With Application
        .ScreenUpdating = False
        .EnableEvents = False
 End With
 
 ThisWorkbook.RefreshAll
 
 With Application
        .ScreenUpdating = True
        .EnableEvents = True
 End With
 
 Calculate
 
 End Sub

I have set up a breakpoint to check the code and after entering something in H15, the macro fires, and then stops at the breakpoint (as it should) but then keeps looping when I start it off again. What have i done wrong?

 

[DanteAmor]

Do you have other codes on another or other sheets?

 

[ajm]

Hola Dante, como estas? I have a workbook open event to hide sheets that are not needed at this point.

 

[DanteAmor]

I performed tests with your code and it does not enter a loop.
Maybe something with your data.

 

[ajm]

Thanks Dante. I think it was having issues as the refreshall command affects 36 pivots, and 40 tables.

 

[DanteAmor]

But you have the events turned off (.EnableEvents = False), this should not enter a loop. I think!

 

[ajm]

exactly. I don't think it was a loop. I rearranged the data and let it run. took a little while due to the number of pivots and tables but it did run. thanks again for looking.

 

[DanteAmor]

With pleasure, thanks for the feedback.