• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic file path updating pivot table using macro

D

Danny

Member
I am currently trying to get a pivot table to update the data source and refresh by making use of a macro.

This works correctly on my local machine, however when i send it to a colleague it errors. This is because when updating the data source its calling my profile rather than a dynamic address. When another user runs this macro obviously the address cannot be found and the code errors.

Is there a way to edit the data source without and make use of a dynamic address rather than a static one? I have attached sample code to help my explanation.

Thank you in advance
Danny
 

Attachments

  • Pivot Table Error.PNG
    Pivot Table Error.PNG
    16.6 KB · Views: 10
Hi Danny,

I note that it is looking for the file int he directory C:\Users\dvickers,

So I think that means the Macro is pointing to the file on the computer that has C:\Users|dvickers as a directory.

So is the file also available on a network drive or is there a copy on the file on the other users computer.

A solution would be to place the whole SourceData in a cell on the spreadsheet and have the macro read it from there, and in the spreadsheet you can change the Source accordingly.

So assuming you put the SourceData in Cell A1 of Sheet1 you could in the macro make mySource = Sheets1.Range("A1")

and then in place of the details you have highlighted in yellow you could put mySource

cheers

kanti
 
Hi Kchiba

Thank you for getting back to me.

I'm not sure if that will work, I'm copying a large amount of data from a report that is given to me. I then insert that data into a worksheet (in the same workbook as my pivot table).

Are you or does anyone know a VBA script that will update the source of my pivot table (and refresh my pivot table) and automatically find the last row with data? The columns from the source data stay the same.

The source worksheet is called: Employee_Forecast_details
The pivot table is called: Pivot3
The pivot table is located on worksheet: Billable_Hours

If you need more information please ask.

Thank you in advance for your help.
 
May I suggest that you convert the source range into an Excel table and then make the table the source of the pivot, in this way the pivot when re-freshed will use the whole table.

I am not sure about your process, it might be preferable to put the source data into the same file as the pivot, so that the pivot is not based on an external file.

Cheers

Kanti
 
Why would the macro build source an external address when the source data is in the same workbook as the pivot table? It seems a little odd. Why wouldnt the address just be the work sheet name that contains the source information?
 
Hi, Danny!
Have you downloaded the file to a local drive or just opened it from a link?
Regards!
 
From your picture that you uploaded it is referring to a file on the C:\......

if you upload your file, I am sure it will become clearer
 
SirJB7,

Apologies for the cross posting, this was not done intentionally. I realized that my first post was in the wrong location (that's why there are two post with a few hours of each other).

Kchiba

I am unable to upload the file due to it being confidental work documents. I have managed to (badly) code around this. Its poorly written code to which i will try and improve later but it works at the moment.

Thank you
 
@Danny
Hi!
Whenever it happens, just post a comment asking to move the post to the proper forum, and some Ninja (no, the keymaster doesn't do that job... no, I don't know if he does any other job... yes, I doubt he knows that word meaning) will do it.
Regards!
 
You must log in or register to reply here.
Back
Top