I have a problem that is stumping me€¦

The default address for the data on my PC is \Tom\Docs\Reporting.

I want it to be \*User*\Docs\Reporting.

Ive tried to update the pivots automatically on open, in ThisWorkbook
module. The code I'm running (below) is lifted straight from the other
modules, so I know it should work. But that's not the problem I don't think.


Public SpecialPathMyDocs As String ' ref GetSpecialFolderMyDocs

Private Sub Workbook_Open()

' set SpecialPathMyDocs as default My Documents folder

Dim WshShell As Object

Set WshShell = CreateObject("WScript.Shell")
SpecialPathMyDocs = WshShell.SpecialFolders("MyDocuments")

'Open folder in Explorer
'MsgBox SpecialPathMyDocs

' set default path for all pivot tables as default MyDocs folder in order to
allow refresh

Sheets("Files Data").Activate

Range("B9").Select

' this is the line that chooses source address
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'" & SpecialPathMyDocs & "\Reporting\[Data UK YTD.xls]UK'!$A:$BC"

With ActiveSheet.PivotTables("PivotTableF1").PivotField s("Category")
.PivotItems("FILES").Visible = True
End With
' refresh pivot
ActiveSheet.PivotTables("PivotTableF1").PivotCache .Refresh

End Sub



The above code results in a runtime error on workbook open: I cant update
the default pivot data address without first refreshing the pivots (hence the
error).

Heres the catch 22. I cant refresh the pivots if the default address isnt
where the data is stored. Obviously on my PC it's already correct, so the
code works with the refresh command first, but won't work if I move the
report to another PC (as is the intention).

The only way I can think would be to save the data with the pivots, which
therefore negates the need to refresh the data before changing the address in
the above code, but also makes the report file massive. I can then write a
macro to remove the "save data with table" before saving.

Is there a better way around this problem?