VBA Producing Error Code 1004

sktneer - post adjusted - THANKS !

Originally Posted by sktneer

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

No problem,

Are you able to help with my problem?

Originally Posted by sktneer

Thanks for changing the Thread Title.

Rudi,

Thank you for the reply - where should I place the code (e.g. module 1 etc.)

What code should go in ThisWorkbook?

Regards,

Originally Posted by RudiS

You could try code like this, but its not all that optimized with the loop statements

Please Login or Register  to view this content.

Rudi,

It work NEARLY perfectly!!

The Macro is doing exactly what I want it to do (thanks for that - I have asked so many people to try and solve it which has proved fruitless!) - However, when I click back on the data table I get the following error:

Run-time error '1004':

Unable to get the PivotTables property of the Worksheet class

..... Debug highlights 'Sh.PivotTables(1).PivotCache.Refresh' within the code .....

I have 7 sheets coded as follows:

• Sheet 1 (Data Table); Sheet 13 (PT1); Sheet 2 (PT2); Sheet 3 (PT3); Sheet 4 (PT4); Sheet 5 (PT5); Sheet 8 (PT6); Sheet 9 (PT7)

Does the code need to be adjusted to compensate for this?

Thanks

Originally Posted by RudiS

Hi,

Open VBA
Double click on the ThisWorkbook module (to open the main module on the right)
Then just copy the code "as is" into that module.
The Event handlers are already set in the code, so VBA will know when to run the macro.

Attachment 328635

Rudi,

Apologies for my ignorance,

The code I tried to enter based on your post is as follows:

Please Login or Register  to view this content.

However, that doesn't seem to be working (Macro stopped altogether)

Could you provide image like before as an example please?

Also with your previous code - when the refresh is occurring ... it flicks through all of the sheets before returning to the sheet I originally clicked on - is there a way to avoid that and instead produce a smoother refresh action?

Thanks for all your time

Originally Posted by RudiS

Hi Luke,

Try this adjustment:

Please Login or Register  to view this content.

or modify Sheet1 to exactly what the name of the sheet (with the data table) is...

BTW: Glad is is almost working

Rudi - I changed 'Sheet1' to 'Data Table' and that has now solved the issue :-D

Is there a way to avoid that flicker during refresh?

If so then that is PERFECT and I owe you big time !!!

Originally Posted by RudiS

Sorry....that was supposed to be

If Sh.Name <> "Sheet1" Then

Rudi,

It works like a dream,

I can not thank you enough :-D

Originally Posted by RudiS

This is untested, by usually switching off the ScreenUpdating property resolves screen flicker.

Try the following version:

Please Login or Register  to view this content.

Rudi,

Sorry to be a complete nuisance ....

I just wondered whether you know what this issue was on the other database I had .... I have the exact same database saved in another folder (with a couple less sheets housing pivots).

I have put your code in that to get it to work the same, which it does and is fine. However, when I open the workbook initially I get the following message appear everytime:

That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data etc. etc

Just the message you get when a sheet is protected - I can click OK and things are fine, but its just a bit annoying - do you know a way around this?

Thanks again

Morning Rudi,

On the new database I removed all passwords. I then entered your new suggestion into ThisWorkbook. I saved, closed and reopened to find the following error:

'Cannot edit pivot table on protected sheet'.

I don't know why the one database works perfectly yet this one is having trouble?

Thanks

Originally Posted by RudiS

The trick is to prevent the code from running the refresh pivot line unless a sheet with a pivot table is selected. Then if a sheet with a pivot table is selected, it must ensure that ALL sheets with pivots are unprotected before the refresh line runs. Since ALL pivots are linked to the same table source, when one pivot refreshes the others linked to that same source will too, and if that sheet is still protected it will debug. So the following code will hopefully take note of these scenarios and handle these various conditions effectively.

Give this version a try:
It seems bloated but I have tried to trim it down as far as possible whilst ensuring that the various conditions are evaluated and prepped.

Please Login or Register  to view this content.

@sktneer
Thanks for that suggestion.
I initially built it into my new code, but than had to remove it again as clashed with the events at the time...

There is a hidden sheet which the pivots vLookup from for a calculation - but that same sheet is in the other database that we have got working - plus I have unprotected all the sheets and still get that error on opening?

If I unprotect all - save (using your most recent code) and open - things work - the 3 sheets with pivots on refresh, however the data table remains unprotected - If I protect the sheets, I get the error seen in the image.

There isn't a debug error - its just a simple cannot refresh pivot on protected sheet.

Do you want to take a look at the sheet?

Protected Sheets.jpg

Originally Posted by RudiS

Morning to you too!

Hmmm...
If you say you unprotected all the sheets, then this code should not debug?
Are there hidden sheets in the workbook?

Rudi,

I have actually just noticed that on the other worksheet that we managed to get working - the hidden sheet that is on there comes up with an error/debug message, however because it was hidden, it seems that your code was bypassing the error and allowing for it to work?

@sktneer - My sincere apologies - I am new to the forum and therefore learning the ropes - it won't happen again!

Thanks

Originally Posted by sktneer

@luke.walker

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

Rudi,

Apologies,

I am new to it all - have replied anyway - Would it be worthwhile sending the workbooks across to you? It seems like your code is making things run great (Data Table and 6 sheets with pivots), however this hidden sheet (predicted height chart) is a table with data in that is used by the data table to produce a calculated value - does the code need to be adapted to compensate for that issue in the original workbook?

Still doesn't resolve the issue in the second workbook however :-(

Thanks for your help I really appreciate it

Rudi how can I provide you with access to my workbook(s)?

Sorry for ignorance

Rudi my email is as follows if you want me to simply send them over that way:

[email protected]