As an aside first, you have reduced the number of categories from 5 to 4, with the last one being
300k-500k, but this category includes values greater than 500k, which is misleading; shouldn't it read
>300k ?
one last question, when you are in the pivot table, under the ribbon tab analyse there is a button called change data source, what is the function of that, can it be used to change the data source to another file.
Well, it could be if the pivot table's source data was a file (which it can be), but your workbook uses a range in another sheet (in this case now, a table). I stuck with using the table rather than connecting directly to a file because I have no idea what else you might be using the source data for in your project.
Instead of updating the table by cutting and pasting, I updated it by using a query/data connection. So your pivot table and chart get updated by (1) updating the table and (2) refreshing the pivot table.
I set up the query for the table as follows.
On a clean sheet, I went to the
Data tab of the ribbon, in the
Get External Data section, I clicked on
Existing Connections, clicked on
Browse for more… at the bottom, in the bottom right dropdown, changed '
All Data Sources' to '
Excel Files', navigated to one of your
Raw Data.xlsx files, and clicked
Open. In the resulting
Select Table dialogue box, chose just the sheet name, made sure there was a tick in the
First row of data contains column headers check box, clicked
OK, in the next
Import Data dialogue box, chose
Table, and (this is where you might instead choose to create a pivot table, in which case you will get a new pivot table - you might prefer this), then I accepted the default
=$A$1 for where I wanted to put the data and clicked
OK.
Now, to get some vba code to change the source file I recorded a macro of my doing the following:
I selected a cell in the Table, went to the
Table Tools, Design tab in the ribbon and clicked
Properties in the
External Table data secion, and in the resulting
External data properties dialogue box, clicked on the
Connection Properties button:
and in the
Connection properties dialogue box, chose the
Definition tab and edited the name of the file:
then clicked
OK,
OK. Then stopped recording.
The only line of importance in the resulting code was
Code:
.Connection = Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Public\Documents\chandoo33868\RawData3.xlsx;Mode=Share Deny", _
" Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB", _
":Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Passwor", _
"d="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OL", _
"EDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Vali" _
, "dation=False")
The part to the right of the equals sign is actually an array, and doesn't need to be, I converted it to a single string:
Code:
.Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Public\Documents\chandoo33868\RawData3.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"
where you can see the filename:
C:\Users\Public\Documents\chandoo33868\RawData3.xlsx
which I replaced with:
" & fn & "
to get:
Code:
.Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & fn & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"
Then I added code to assign something to
fn.
Now you can do it yourself!