Add variable to pivot table code

[yeongkwe]

I want to add in a variable from pivot table using VBA. Please kindly advise me. Your help will be very much appreciated. Thanks you.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+v
'
cdcd = Worksheets("sheet8").Range("AQ2")

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .PivotItems("cdcd").Visible = True
        .PivotItems("2013").Visible = True
    End With
End Sub

 

[RoryA]

Remove the quotes round the variable name.

 

[yeongkwe]

Hi, I try your way remove the qoutes (cdcd) still cannot. : (

 

[RoryA]

What error do you get with this

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+v
'
dim cdcd as string
cdcd = Worksheets("sheet8").Range("AQ2")

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .PivotItems(cdcd).Visible = True
        .PivotItems("2013").Visible = True
    End With
End Sub

 

[yeongkwe]

Hi, I get this pop outbox stated, Unable to get the pivotiems property of the pivotfield class. But after you send me a good example, I can my dim as cdcd as long to string, the code works already. By the way how come like that? string and long for different in coding? sorry I think I had ask you a stupid question. Thanks for your help. you are really good.

 

[RoryA]

You can refer to PivotItems by name or by index position. If you pass a year as a number, the code will think it is an index position and look for say the 2013th item, rather than the item called "2013". Using a string solves that.