Macros/VBA - assistance required

[bspan]

Hi, I've a sheet with an ODBC datapull into it. I also have two manual macros (buttons) on this sheet that I'd like to automate to run everytime the datapull is refreshed (set to 2minutes). I've found the below code and followed it but it's not working...

From: Automatically Run Macro when Cell Changes |
---------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then

Application.EnableEvents = False

'ENTER YOUR MACRO HERE THAT WILL RUN WHEN CELL CHANGES

Application.EnableEvents = True

End If

End Sub
---------------

The two macros I have that I need to be included are....

---------------
Sub AllWorkbookPivots()Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables
pt.RefreshTable
Next pt

Next ws

End Sub
---------------
Sub Update_All_Manually_Calculated_Fields()
'
' Update_All_Manually_Calculated_Fields Macro
' Columns R-V
'
'
Range("R2:V2").Select
Selection.AutoFill Destination:=Range("R2:V1947"), Type:=xlFillDefault
Range("R2:V1947").Select
Range("Q1911").Select
Selection.End(xlUp).Select
Range("Q797").Select
End Sub
---------------

My sheet which the code needs to be entered into is 'Sheet4.All Open Tickets'.

 

[wigi]

Hello

For example:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim pt As PivotTable
    Dim ws As Worksheet

    If Target.Address = "$A$1" Then

        Application.EnableEvents = False

        For Each ws In Me.Parent.Worksheets
            For Each pt In ws.PivotTables
                pt.RefreshTable
            Next
        Next
        
        Range("R2:V1947").FillDown

        Application.EnableEvents = True

    End If

End Sub

It appears that you're new here, welcome !
Can we ask to use

 tags please? Thanks.

 

[bspan]

Thanks, this does work if I now update cell A1 manually. As the datapull is only marginally different everytime if isn't seeing this as a change.
I tried to set AA1 as

 =NOW()

, so that this auto refreshed when the datapull ran - but this also doesn't see it as a change even though the time changes.

Is there a way that it can just tell when the ODBC connector refreshes and run the macros based on that rather than a change in a particular cell?

Thanks,
BSpan

 

[wigi]

Hello

I'm not sure this is possible. See here for (coded) workarounds:

running macro after refresh of extenal data

 

[bspan]

Ok, how's about another train of thought to get to the same requirement...

Can the macro be set to run every 2 minutes, rather than being triggered by a change in a cell?