i'd tried a few different ways of doing this and none worked till i found your example, thank you so much for explaining and showing clearly. Much appreciated, keep up the great work
Great! Works really well. 👍 But, if your Pivot Table is password protected (so people cannot make changes) XLS gives a " Run-time error '1004' ". Is there any way you can keep the Pivot Table Sheet password-protected and run your macro? Thank you
Congrats for the outstanding video! However, when I try to select "worksheet" at Visual basic, I get and error message - Variable uses an automation type not supported by visual basic". Any idea why this is happening and how to fix it? Thanks
Hi there, I tried doing this however, I need the worksheet to be shared with others and VB coding cannot work when you're sharing a worksheet. Do you know how I can do this but also share the worksheet at the same time? Thanks!
Great video! I have a further question: What if my source data originates from multiple sheets? Example: Cell A1 is: =SUM(Januar:Desember!K6) When i make a direct change to my source data my pivottable will update. When i make a change in any of the sheets from january to Decmber, it wont update untill i manually press refresh. I have already typed your code. Thanks!
Hi when I am updating any data in the pivot table its working but when i am adding data then the newly added entry is not reflected. Could you please help on this?
Is there a way to apply this concept within a single worksheet? I'm working on an inventory program and would like inputs in cells in column c to be added to the cells in column b, then cleared
@@Computergaga essentially I would like to have a system where I can have a product with its inventory quantity marked in cell s1 for example, and then have cell b1 where I can input a recieved quantity and that number be automatically added to a1's number then have b1 cleared to be ready for a new input
As our data increasing by adding more data, don't we have to change the data source? Does it automatically change the source or we have take to take extra source beforehand.
Hi Suraj, in the video the PivotTable is created from a Table. A Table will update itself as the data source increases so this is recommended. If the Pivot is created from a standard selected range then the range will have to be updated each time. This can be included in the code.
@@Computergaga Sir, I am creating pivot table using source which I am adding through user form. When i am updating data it is reflected but when adding it is not reflected in the pivot. Could you please help to create a video on it?
May I have 1 question? if the pivot table data is linking to another date. For example in the pivot table data, the amount is "=Sheet2!F1". Even if i update the number in Sheet2 F1, the pivot table cannot refresh. Unless I physically click sheet 1. Do you have a solution?
Thanks for the video. One question, I'm generating a report using vba and I'd like it to be a table or to have a format, the number of rows is constantly changing I don't know what to do.
Here is a fantastic article on using tables with VBA - www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables. Tables will control the formatting and ever changing row number.
It is absolutely possible Muhammad. I assume you mean the PivotTable itself and not the source data. Unfortunately I do not have a video for this process. If you are new to VBA, I suggest using the macro recorder and recording yourself copy and pasting a finished PivotTable to see what code was generated.
For some reason I put in the same code as you for my dataset using my pivot name in quotes and both of my pivots updated. And that was using the first method you showed that should only update in my case "Madden_Pivot". The other pivot was just "PivotTable1". Any idea?
Sounds like both PivotTables are using the same cache. Its quite common. Normally created by copy and pasting a PivotTable to get a new one instead clicking Insert > PivotTable for the second one. Normally quite helpful, but if you don't want it, create the other Pivot with a different approach.
If i try to modify the existing data, i am getting error as "Run time error 9" Subscript out of range. Please help me on this. i am using excel 2013 ....
I would double check the worksheet or table names you may have used. This type of error indicates that something you are referencing is outside of its range i.e. referencing worksheets(4) when there are 3 worksheets in a book.
My code isn't working either. I am just doing the last method where it should update them all. I read in the comments below that you shouldn't copy and paste pivot tables. is that true? I get a runtime error on mine as well.
This error typically occurs when an object is not defined correctly (mis-spelt or mis-typed). This is easily done when typing workbooks and worksheets. So I would check the typing, however it could be a number of things. Another one is referring to the name of a sheet, PivotTable or workbook that does not exist.
Hi Sir, I have the same issue now. In my case, I just tried to deleted the rows in a sheet and adding them back to the same sheet. I am getting run time error with application /object not defined. Can you please advise
i'd tried a few different ways of doing this and none worked till i found your example, thank you so much for explaining and showing clearly. Much appreciated, keep up the great work
You're welcome Tailzer. Thank you.
Great! Works really well. 👍
But, if your Pivot Table is password protected (so people cannot make changes) XLS gives a " Run-time error '1004' ". Is there any way you can keep the Pivot Table Sheet password-protected and run your macro?
Thank you
@Computergaga You explain things so well. Thank you so much.
You're very welcome.
No i have no words "that is truly Awesome !!!!!!!!!!!!!!!!
Thanks Muhammad.
Hi, Thanks for the great video! Is it possible to schedule the vba code without evening the workbook?
Absolutely amazing video! Thank you so much!
You're welcome Adam.
Brilliant!!! Thank you for this, Awesome!
You're welcome. Thank you.
Congrats for the outstanding video! However, when I try to select "worksheet" at Visual basic, I get and error message - Variable uses an automation type not supported by visual basic". Any idea why this is happening and how to fix it?
Thanks
Very nice - it's almost worth saving this code in the new workbook template
Thanks Grabsplatter.
Thank you. Solved my purpose.
Awesome!
Thanks. Very useful and well presented.
Thank you Ujjwal.
Hi there, I tried doing this however, I need the worksheet to be shared with others and VB coding cannot work when you're sharing a worksheet. Do you know how I can do this but also share the worksheet at the same time? Thanks!
As long as the macro is saved to the workbook and the references to the sheet and PivotTable name are consistent then it should work.
Good job man! Thank you
Thank you, Shakhobiddin.
Great video! I have a further question:
What if my source data originates from multiple sheets?
Example: Cell A1 is: =SUM(Januar:Desember!K6)
When i make a direct change to my source data my pivottable will update.
When i make a change in any of the sheets from january to Decmber, it wont update untill i manually press refresh. I have already typed your code.
Thanks!
Many thanks mr. Alan on this code
My pleasure.
Heaven sent. Thank you Sir.
Thank you Samuel.
Hi
when I am updating any data in the pivot table its working but when i am adding data then the newly added entry is not reflected. Could you please help on this?
I have difficulties in setting up an auto refresh a pivot table when the source data is the output of power query. May I know how to solve this issue?
Is there a way to apply this concept within a single worksheet? I'm working on an inventory program and would like inputs in cells in column c to be added to the cells in column b, then cleared
I'm not exactly sure what you mean, but yes with VBA we can run and clear formulas.
@@Computergaga essentially I would like to have a system where I can have a product with its inventory quantity marked in cell s1 for example, and then have cell b1 where I can input a recieved quantity and that number be automatically added to a1's number then have b1 cleared to be ready for a new input
As our data increasing by adding more data, don't we have to change the data source? Does it automatically change the source or we have take to take extra source beforehand.
Hi Suraj, in the video the PivotTable is created from a Table. A Table will update itself as the data source increases so this is recommended.
If the Pivot is created from a standard selected range then the range will have to be updated each time. This can be included in the code.
@@Computergaga Sir, I am creating pivot table using source which I am adding through user form. When i am updating data it is reflected but when adding it is not reflected in the pivot. Could you please help to create a video on it?
May I have 1 question? if the pivot table data is linking to another date. For example in the pivot table data, the amount is "=Sheet2!F1". Even if i update the number in Sheet2 F1, the pivot table cannot refresh. Unless I physically click sheet 1. Do you have a solution?
Great video Mr.Alan. Many thanks!
Thanks for the video. One question, I'm generating a report using vba and I'd like it to be a table or to have a format, the number of rows is constantly changing I don't know what to do.
Here is a fantastic article on using tables with VBA - www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables. Tables will control the formatting and ever changing row number.
Amazing. Thank you so much!
You're welcome.
Very useful thanks!
Thank you Alex.
Brilliant! Thank you.
You're very welcome Narendra.
Hi sir, I'm getting error on
pt.Pivot.cache.refresh
Can you please help?
I’m getting the same error...
I want to copy and paste pivot table data on sheet3 with VBA is that possible, if it is please guide me or share your video you have
It is absolutely possible Muhammad. I assume you mean the PivotTable itself and not the source data.
Unfortunately I do not have a video for this process.
If you are new to VBA, I suggest using the macro recorder and recording yourself copy and pasting a finished PivotTable to see what code was generated.
Thank you so much!!!
how to make it same as like the pivot but with the combo box ?
For some reason I put in the same code as you for my dataset using my pivot name in quotes and both of my pivots updated. And that was using the first method you showed that should only update in my case "Madden_Pivot". The other pivot was just "PivotTable1". Any idea?
Sounds like both PivotTables are using the same cache. Its quite common. Normally created by copy and pasting a PivotTable to get a new one instead clicking Insert > PivotTable for the second one.
Normally quite helpful, but if you don't want it, create the other Pivot with a different approach.
Ah! That's it! Thank you!
Your welcome.
I have noticed that years were gone in pivot table after putting vba code. Please kindly tell me what happened.
thanks dude it work
Excellent.
Good explanation
Thanks Kana.
If i try to modify the existing data, i am getting error as "Run time error 9" Subscript out of range. Please help me on this. i am using excel 2013 ....
I would double check the worksheet or table names you may have used.
This type of error indicates that something you are referencing is outside of its range i.e. referencing worksheets(4) when there are 3 worksheets in a book.
hi i dunno why this codes dont work to me :( i saved my excel as macro enabled but still it dont work :(
Do you get an error message?
Check the PivotTable name matches.
My code isn't working either. I am just doing the last method where it should update them all. I read in the comments below that you shouldn't copy and paste pivot tables. is that true? I get a runtime error on mine as well.
You can copy and paste PivotTables. That is fine. They will use the same Pivot Cache.
Not sure why the error message. Would need to see the code.
this is my fault.
pt.pivotcache.refresh
All sorted?
I have tried exactly being said still i cant get it updated automatically, what could it be i missed
May be macros are not enabled on the file?
If not will have to check the steps again. Something must be missing. Do you get an error in the code?
@@Computergaga I did receive an error code and it asked me if I wanted to debug and took me to the line of code. Any idea what I could do?
Please share the file
Run time error '1004'. How can I solve this issue please?
This error typically occurs when an object is not defined correctly (mis-spelt or mis-typed). This is easily done when typing workbooks and worksheets. So I would check the typing, however it could be a number of things.
Another one is referring to the name of a sheet, PivotTable or workbook that does not exist.
Hi Sir,
I have the same issue now. In my case, I just tried to deleted the rows in a sheet and adding them back to the same sheet. I am getting run time error with application /object not defined. Can you please advise
Great video, but I am having trouble getting it to work. I am going to try to send you an email. Thank you!
Thank you, David.
10x man
:)
Didn't work
Sorry to hear that. Have you re-watched and tried the video steps?
thank you so much!
You're welcome Julian.