CONCATENATE in VBA equation

[Larsen]

Hi Everyone,

I am trying to pull some data from a pivot table and store it in a variable. I need to use the CONCATENATE function in order to pull the different parts of the pivot table together because I am using a variable called Price_Band.

Right now I am just outputting it to the active cell so I can see if it works.

I keep getting the error "Sub or function not defined"

I am not sure if I need to call a library since I am using the CONCATENATE function in VBA which is a first for me. I have created this equation in a cell and it works.

Can anyone out there please let me know if they see something wrong.

Sub Get_Volumes()
Dim Green_Book As PivotTable
Dim Price_Band As String

Price_Band = "1700-1799" ' this is a price band in my pivot table so doing this here to assign the variable. Once this works this will come from a cell in the excel sheet.

Set Green_Book = Sheets("Green_book").PivotTables(1)

ActiveCell.Value = Green_Book.GetPivotData("[Measures].[CPU Units]", "[DIM OEM].[OEM]", "[DIM OEM].[OEM].&[Acer]", "[DIM Sub FF].[Sub FF]", "[DIM Sub FF].[Sub FF].&[Trad]", "[DIM Quarter].[Quarter]", "[DIM Quarter].[Quarter].&[2009Q2]", "[DIM System PB].[System PB]", CONCATENATE("[DIM System PB].[System PB].&[" & Price_Band & "]"))

End Sub

FYI - I am a intermediate C++ programmer who taught myself VBA so feel free to make suggestions.

Thanks!

 

[VoG]

Try losing the CONCATENATE (that is a worksheet function)

ActiveCell.Value = Green_Book.GetPivotData("[Measures].[CPU Units]", "[DIM OEM].[OEM]", "[DIM OEM].[OEM].&[Acer]", "[DIM Sub FF].[Sub FF]", "[DIM Sub FF].[Sub FF].&[Trad]", "[DIM Quarter].[Quarter]", "[DIM Quarter].[Quarter].&[2009Q2]", "[DIM System PB].[System PB]", "[DIM System PB].[System PB].&[" & Price_Band & "]")

 

[Larsen]

Thanks VOG it works!!! who would have thought taking something away would make it work. I will remember that for the future.