Code to Change Data Source for an online shared file, with out using the online link?

I have been working on a Sheet for use at work, that will eventually be spread to multiple offices
Every office will have its own set of data to use.
basically, it sorts it to a Pivot table

Macro I made to automate the time consuming task, in general works great, but needs to have the Data Source refreshed every time...
What happens is it seems to not know to refresh the entire data set... I dont know how to describe it other than if I only have 1400 lines in the data set after initial sorting and removing of extra data by using my macro, the next time the data is refreshed, it only looks at that same 1400 lines
and I need it to change the Pivot data set to look at about 5000 lines every time I run the macro

Right now when I create a Macro and Just do it manually to see what it does, I get this

Range("A2").Select
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"https://filelocation.com/personal/online_com/Documents/[Report In-Out.xlsm]QC Outbound!R1C1:R5000C13" _
, Version:=6)
Sheets("QC Outbound").Select

but becasue this will be shared by many offices, and each office uses their own online location, I need it to be generic and not include a web link..

Is there an "Active sheet" type of command I can put in to update the Pivot data source instead of the full file location?

Originally Posted by dflak

What will they be connecting to? Another workbook an Access File or CSV file?

Their own version of this worksheet

Microsoft Sharepoint One Drive File

they will be Downloading a Hard Copy every Day, making changes to the base set of data, then saving it and it auto uploads...

Sorry, That does not work for my application
I need it to do something specific as I have described...
I just need the VBA code to generically update the Excel sheet pivot table data so I can tell it to update it locally instead of telling it to use the original source from online


I'll explain the sheet so you can have a better understanding

Our company has 35 offices worldwide, each with a different set of equipment(or as I will say, "Gear") needs every day
we have customers that request gear, we package it, rent it to them, then they return it
if they need something that is not in their current location, we truck it in

Daily our list of outgoing and Incoming Gear Changes at each location
the main tracking software we use can spit out a list with all of teh incoming and outgoing gear...(but it is a large list with ALL of the different departments all at once)
we take that list, and copy/paste it into a worksheet, which sorts it into a pivot table...

basically drop data into Sheet1(outgoing gear) and Sheet2(Incoming Gear), sort with a macro to get rid of things not used/needed by our department
the problems that happens, is that the data set size changes each day, and becasue we filter and delete lines, the pivot data set also changes...
it often cuts off many lines... unless I refresh the data set used and tell it to use A1:M5000 of the Sheet 1
I want to know how to tell it to do that for sheet1 and not care about the file name or online source

we store the sheet online using excel online thru office 365/onedrive (a seperate excel sheet per office location)
then each person in the department can "Color" the cells of the items they have completed...
making it so the manager can see what has been done, and what still needs to be done....
since sometimes gear comes in on an early truck, sometimes late, and everything has a deadline to go out depending on if its a cutsomer specified pickup or if its going on a freight truck, or UPS, or Fed Ex... we have to know what is done and what is not....
this sheet makes it so we can see when an item is supposed to go out, and if its not done before its time to go out, we make a point to hurry up and find it and get it ready so it can make the deadline time

because we store it online for "real time changes" across many computer stations
in the morning, the manager will open the web location using onedrive online, and then you can select "Edit in Browser" for real time editing...but you cant update the pivot table online
so instead, you can select to "Edit in Excel" which downloads the file and opens it locally on your computer....
then when you are done, you hit save, and close it out and it automatically re-uploads and overwrites the file online so its ready for everybody to edit again.


hopefully this clears ups what we are doing so you can understand what I need it to do as well...

Like I said...
I just need to know the VBA code to use to tell it to update the data set used without having to tell it a file name or an online location of the file

as an example....
Macro says this

ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"C:\Users\Scott\Desktop\[2017-03-03 QC Equipment Report In-Out.xls]QC Outbound!R1C1:R20000C13" _
, Version:=xlPivotTableVersion10)
Sheets("Pivot Inbound").Select
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"C:\Users\Scott\Desktop\[2017-03-03 QC Equipment Report In-Out.xls]QC Inbound!R1C1:R20000C12" _
, Version:=xlPivotTableVersion10)

can I get rid of the File location and replace it with some sort of "This workbook" command

Originally Posted by dflak

If you are going to copy and paste data in, if you do it to excel tables, your pivot table will read exactly the data in the excel table regardless of how much data is imported. Then you can filter based on whatever the office ID is. All formulas running off the table also reference exactly the right amount of data.

you arent quite understanding what is happening....
Its not like you are thinking

I just need a solution to Change the Pivot chart Data Set Just like in the VBA code I listed above... without having to tell it the file reference (the part that says "C:\Users\Scott\Desktop\[2017-03-03 QC Equipment Report In-Out.xls")

Originally Posted by dflak

I do not understand. How do you expect the program to know what the new data source is if the user does not define it?

It should not need to know a File...
it only needs to know it should reference the "QC Outbound" Sheet in the workbook the pivot table is in......and not care about any other file/workbook....
it shouldnt be looking for a source other than itself...nor do I want it looking for a source other than itself....

I need VBA code that instead of saying a file location... it just says..."reference your own workbook only"

Originally Posted by dflak

I think I might have it now. There is a QC Outbound Sheet in the workbook. There is data in this sheet and it does not matter how it gets there. Is this correct? So what you need is your pivot table built off this data.

You can define the data as a named dynamic range and build the Pivot Table off of it. First create the pivot table. Then create the named dynamic range. Then change the source of the pivot table to the named range.

The named dynamic range will act like an Excel table. As the user imports / copies & pastes or otherwise gets the data to the page, the dynamic range will accommodate it. No VBA is required.

This is Already what we are doing....
Problem is not when we update the data, the problem is that we sort and delete rows out of the main data each day...(before the pivot table refresh)
which in turn changes the dynamic range values of the pivot table...
and we need to reset the dynamic range or the pivot table doesnt use all the data

Found somebody else who could help me out....
This is what I was Looking for.....
again... was looking for a "This Workbook" type of command....
and this is it.


ActiveSheet.PivotTables("PivotTable1").ChangePivotCache _
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="QCSheet!R1C1:R20000C12")
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh