Pivot table source data & Dropbox

Dropbox is an indispensable tool to have these days as it's such an efficient way to share files with your clients & of course it's free. However on occasion, if an Excel file is opened directly from the Dropbox website (as opposed to the locally stored Dropbox folder) the pivot table source data retains the Dropbox path. If the file is reopened locally later on or linked back to a report in Alchemex then you will find a whole heap of trouble. So, always save the file locally before edititing.

Listed below is some useful VBA code to list the pivot table source in all the tables within a workbook:
(An edited version from gaj104 on MrExcel.com)

Sub PivotSouceData()

Dim pt As PivotTable
Dim s As Worksheet, ws As Worksheet
Dim i As Long

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "PivotRanges"

Set ws = ActiveSheet
ws.Activate

ws.Range("A1:C1").Value = Array("Sheet", "PivotName", "DataSource")

i = 2

For Each s In Worksheets
For Each pt In s.PivotTables

ws.Cells(i, 1).Value = s.Name
ws.Cells(i, 2).Value = pt.Name
ws.Cells(i, 3).Value = pt.SourceData
i = i + 1

Next pt

Next s

ws.Columns.AutoFit

End Sub

Ed Ferrero also has some useful coding to handle pivot table formating.

training

tv

On line via the Alchemex website

On site in NSW Sydney Region, the training is taylored to your needs, be it Alchemex or Excel.

 

report writing

Business Reports directly in Excel at the touch of a button using Alchemex.

All the major accounting packages are supported: MYOB, ABM, Sybiz, Sage packages (Handisoft, Accpac etc)

Excel & Alchemex Tips

Alchemex Tips & Tricks
There's often a little gem in the Alchemex Tips & tricks.

Excel Tips & Useful sites
Chandoo and Ozgrid are very useful......

© Orginal Template by Flipside Digital