Pivot table refresh issue with SQL

[jkchild]

Hi

I have an excel file that updates 1 tab based on an sql program.

I then create a pivot table in a new tab.
When I refesh the sql, it removes the data and updates with new data in the same tab.

this means that when i refresh the pivot table it thinks I deleted the data and gives me an error, is there a way I can lock the pivot table range after the sql refresh??

thanks
J

 

[mole999]

if you make your sql dump a named range, then create your pivot from the named range that should work

    Workbooks(XXX).Names("AltRes").Delete

    Range("A1").Select
    AA = Range("a20000").End(xlUp).Row
    zy = "='VehicleKey'!R2C1:R" & AA & "C9"

    Workbooks(XXX).Names.Add Name:="AltRes", RefersToR1C1:=zy

 

[Fazza]

Mole's answer should be fine without any VBA. That is, just give the dataset a defined name, and make it the source data for the pivot table. The range of the name should adjust automatically each time the query is refreshed.