Pivot table ordering VBA

aleatoricallysimple

New Member
Joined
Mar 29, 2011
Messages
3
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:

Code:
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:

Code:
(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?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You need quotes around your cell addresses in the Range calls, and it's IsNumeric:
Rich (BB code):
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

New Member
Joined
Mar 29, 2011
Messages
3
You need quotes around your cell addresses in the Range calls, and it's IsNumeric:
Rich (BB code):
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,649
Messages
5,597,368
Members
414,139
Latest member
okela0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top