Pivot table ordering VBA

[aleatoricallysimple]

Hi all,

i'm new to the forum so sorry if I make any message faux pa's.

I'm doing a bit of a pivot table and I've got a bit stuck in some of the coding and I was hoping someone would be able to hlep me out a bit.

I've written a vba so far that basically uses a checkbox to decide the filters for my pivot. (I wish work had excel 2010 as then I could have just used the slicer function.)

I've got so far the filters on and off, the problem is because there was a bit of a weird date naming convention the order for the months isn't working too well as they are considered text and not months - that or when I try using the filter names in can't find them.

So to get around this fact as its not that practical to change the dates - used a couple of formulas to create a list of the right order of filters in an excel spreadsheet. therefore the range is:

Sheet = "Admin"

range B2:B12

contents

B2=sep-10
B3=Oct-10
B4=Nov-10

etc.

What I want to so is say in the vba:

If A2 contains a number then use the content in cell B2 to define the order of the pivot table.

Code so far:

(within sub)
Dim pos1 as string
Dim pos2 as string
Dim pos3 as string
Dim pos4 as string
Dim pos5 as string

pos1 = worksheets("Admin").range(B2)
pos2 = worksheets("Admin").range(B3)
pos3 = worksheets("Admin").range(B4)
pos4 = worksheets("Admin").range(B5)
pos5 = worksheets("Admin").range(B6)


Worksheets("Admin").Activate

If worksheets("Admin").range(A2) = ISnumber? then with ActiveSheet.PivotTables("Data PT").PivotFields("Month").PivotItems(pos1)Position = 1

End With
End If


If worksheets("Admin").range(A3) = ISnumber? then with ActiveSheet.PivotTables("Data PT").PivotFields("Month").PivotItems(pos2)Position = 2

End With
End If

If worksheets("Admin").range(A4) = ISnumber? then with ActiveSheet.PivotTables("Data PT").PivotFields("Month").PivotItems(pos3)Position = 3

End With
End If

'etc for last 2


End Sub

Can anyone help me out please?

 

[aleatoricallysimple]

*bump*

 

[RoryA]

You need quotes around your cell addresses in the Range calls, and it's IsNumeric:

If IsNumeric(worksheets("Admin").range("A2").Value) then ActiveSheet.PivotTables("Data PT").PivotFields("Month").PivotItems(pos1).Position = 1

You should also be able to create a custom list with the dates in the order you want and then sort by that.

 

[aleatoricallysimple]

You need quotes around your cell addresses in the Range calls, and it's IsNumeric:

If IsNumeric(worksheets("Admin").range("A2").Value) then ActiveSheet.PivotTables("Data PT").PivotFields("Month").PivotItems(pos1).Position = 1

You should also be able to create a custom list with the dates in the order you want and then sort by that.

hi, sorry it took so long to reply - work got me doing a different excel and this got sidelined!

Cheers, I've done this and it almost works, the one this that doesn't is the pivotitems that is defined by pos1 isn't found, instead it says

'unable to get the pivottables propert of the worksheet class' so my logic was

dim pos1 as string makes this text (which will be the test to filter)
pos1 = and the range tells it what it is

pivotitems(pos1) will take the contents of the range from pos 1.

However this isn't the case?

How would I set up a custom sort where I can define them by whats in a cell? As atm, I cna't find a non vba way.

thanks so much for your help so far, I'm in the early stages of vba learning - soon to be better!

 

[RoryA]

The error you mention would occur if the pivot table you specify does not exist: using the wrong name or index number, or having the wrong sheet active if you specify activesheet would all cause that.