Replies

I changed the name, but it still didn’t work. The only way it actually ‘did something’ was when I took out the ‘Dane’ as in RowFields:=”Dane”. However it stopped working as soon as it hit a next occurance of ‘Dane’, which in this case was (copied from the original macro): ActiveSheet.PivotTables(“Tabela przestawna1”).PivotFields(“Dane”).PivotItems( _
“Maksimum: ZUNI”).Position = 1

I checked in the raw data and I had a column heading called ‘groups’ but Dane didn’t exist in the raw table at all. I have no idea what it is actually.
Nevertheless, I got a copy of the file that my colleauge succesfully ran in Poland, and this is how the Dane is displayed in his report (see attached file).
Any further ideas on how to solve this?
Thanks in advance!
K.

I changed the name, but it still didn’t work. The only way it actually ‘did something’ was when I took out the ‘Dane’ as in RowFields:=”Dane”. However it stopped working as soon as it hit a next occurance of ‘Dane’, which in this case was (copied from the original macro): ActiveSheet.PivotTables(“Tabela przestawna1”).PivotFields(“Dane”).PivotItems( _
“Maksimum: ZUNI”).Position = 1

I checked in the raw data and I had a column heading called ‘groups’ but Dane didn’t exist in the raw table at all. I have no idea what it is actually.
Nevertheless, I got a copy of the file that my colleauge succesfully ran in Poland, and this is how the Dane is displayed in his report (see attached file).
Any further ideas on how to solve this?
Thanks in advance!
K.

Thanks a lot, that makes sense. I change the chart type from stacked to regular line and now it works fine. Will also keep in mind the tip with the stacked line for future reference.

Rgds,
Kislany

Thanks a lot, that makes sense. I change the chart type from stacked to regular line and now it works fine. Will also keep in mind the tip with the stacked line for future reference.

Rgds,
Kislany

Ok, thanks, I’ll give it a try and if it doesn’t work, will post back.

Regards,
K.

Oh, brilliant, the link works now fine, thanks a lot. Great idea with naming the ranges, haven’t thought of that.

Regards,
K

I ran the codecleaner and followed you steps, when I now saved the file (didn’t give me error messages this time) I got a filesize of 21 Mb (down from 75, but still way too much compared to the < 1M I was having before. I can't send it like this to the client unfortunately, not with this file size…
Does it mean the file has to be rebuilt from scratch. I suppose? It's a lot of work in there and I was trying to avoid that if possible…

Regards,
K

Hm, that is also a solution, haven’t thought of it, thanks a lot.

I found meanwhile something else that works, I used the Y Axis value and linked the second cell to that. Then just repositioned it and it prints now fine.

Thanks again,
K.

Thanks, great idea, just what I was looking for.

Regards,
K.

Hello,

I ran your macro, however I get at the line:
If oItem.RecordCount = 0 And Not oItem.IsCalculated Then oItem.Delete
a Runtime error 1004:’Application defined or object defined’ error

Unfortunately my knowledge of pivot tables (and macros with this regard) is very limited, so I don’t know what to look for…

Thanks,
K.

Just to show you the final result of what I wanted to achieve. I’ve resized it, etc, and you can see the difference between this and the ‘regular’ check-boxes.
Thanks to Andrew, I did it. For some reason it didn’t work before, but it is fine now.
Thanks again,
K.

Hi Andrew,

Thanks a lot for the attachment and the help. it is very good indeed, except for the actual character that I get as caption, when I click on it, the tick-mark changes to a flower kind of thing, see in the attachment, and I’m not sure I understand why…I tried it on another computer as well, and I got this other character there too.

Thanks,
K.

Thanks for all your input on this problem, I know it’s pretty complex, I’m struggling with it for a while now.
Since the 0 which denotes the end of the range is also used in the formula for something else (as an actual data for products that do not have information in them), is it possible to change that first 0 to something else (in my example column M, which denotes the end of the range) and also that reference to the 0 to whatever will be changed in the column M? Maybe in that case the formula will not stop at the first occurance of 0, because it will not search for it, for example if I replace the 0 with a * or any other character…just a thought. But how can I change then the formula to reflect the changes?

Thanks,
K

Yes, I actually did that, and it works fine in this particular case. However I haved a large number of data and while some rows start with actual data, some start with 0. It basically keeps track of products and when they entered the market. Not all the brands entered the market in January/February, some in the middle of the year, for example, so the data before that is 0. I was thinking of a generic solution that finds where the data actually started, rather than me going through 1000’s of lines and fixing each of them separately…

Thanks,
K

Thanks for your help.

I tried out what you suggested, and I got the error message: Unable to set the Subtotals property of the PivotField class

Here is the relevant part of the code:

with

With .PivotFields(“RSE”)

.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
‘in debug mode, it fails at that line with the above message
End With

end with

Regards,
K.