Pivot Table Automation

I was hoping that someone could give me some direction.

I have a pivot table in a main sheet. The data is from another excel sheet
in another file. The data file's name and length may change. For example: the
file name may change from "data 06-30" to "data 07-07"; the rows containing
data may change from 1000 to 1020.

How to use Macro to automatically update the pivot table from the
periodically changing datasource?

Thanks for any help.

Jon

I tried below program, but it doesn't work. Can someone help me? Thanks.

Sub LoadData()
Dim pv As PivotTable
Dim FileName As String

FileName = Application.GetOpenFilename

For Each pv In ThisWorkbook.Sheets("Sheet4").PivotTables
pv.SourceData = "'U:\Projects\5-Macrotize Reports\[" & FileName &
"]Sheet1'!A1:O30000"
Next


End Sub

"Jonathan" wrote:

> I was hoping that someone could give me some direction.
>
> I have a pivot table in a main sheet. The data is from another excel sheet
> in another file. The data file's name and length may change. For example: the
> file name may change from "data 06-30" to "data 07-07"; the rows containing
> data may change from 1000 to 1020.
>
> How to use Macro to automatically update the pivot table from the
> periodically changing datasource?
>
> Thanks for any help.
>
> Jon