Sort a Pivot Table Based on a Range

[JimmyA]

Hello All

I wonder if someone can tell me if it is possible to sort rows in a pivot table based on a range in the same workbook.

I have the pivot table set up as I need it but the values in the row change each time the macro is run (it is run when new data is added)

As part of the macro a list of Grades is imported into the Workbook. These grades are always imported in the correct order. As such I would like the pivot table to follow the order of imported grade list. The grades in the list will always be the same as the list in the pivot table.

Is this something that would be possible to do?

I hope I have explained well enough what I am trying to do but if not please let me know.

Thank you very much in advance for any suggestions.

Regards

Jim

[DonkeyOte]

You could possibly create a custom list based off your grades, use the Custom List as basis for PT sort.

How much of a pain the above would prove to be will in part depend upon how often the grade list would require alteration.

Might be an idea to post a sample file which illustrates what you're working with (dummy names etc...) and also ensure code being used is present.

When dealing with PT's it's also important to stipulate version as pre2007 and 2007 (and beyond) PTs are slightly different.

[JimmyA]

Hi DonkeyOte

Thanks for your very quick responce. I have attched a very edited version of what I am working with but hopefully it should explain what I am trying to do.

The Grade list Sheet shows the order I need the pivot table to be in.

Would this be possible with a named range do you think?

The main issue with this is that everytime this will be used the grades could be totally different.

Thanks again for any advice you can offer.

Regards

Jim

[DonkeyOte]

Jim, it's hard to know exactly what to advise given in the sample the Grade List does not match the PT Grade Field.

Are we to assume that each grade in Data!A:A will exist on Grade List Sheet ?

[JimmyA]

Sorry, in my haste to make the example I forgot to put the same grades in both.

This is what it would look like.

Thanks again.

Jim

[DonkeyOte]

If per the last example we assume each grade listed on Grade List will appear at least once in the Pivot then you could use something along the lines of:

[removed - duff code - see post #8]

I hope that helps in some form or other.

[DonkeyOte]

Jim, scrap the above - it won't work... I'll get back with a revised version when I get a mo.

[DonkeyOte]

As promised...

Please Login or Register  to view this content.

Difference from earlier is that each grade is processed in reverse and placed in 1st position - as each grade is positioned so earlier grades positioned move down the listing... the earlier version would not have worked given positioning was relative to initial positioning.
The above will also account for any grades listed on Grade List sheet that do not exist on Data Sheet.

[JimmyA]

Thank you very very much for your help.

This is exactly what I need. I am going to study this to understand fully how it is working.

Thanks again.

Jim