Pivots using VBA Macro

[Sameer.k21]

Hi All,

I am trying to write a macro to create multiple pivots in 1 sheet, like one pivot below the other.

please help me, how do I Identify last row + 1 and place the second pivot, then third and so on. I want total of 7 pivots from same data source.

Regards,
Sam

 

[Sameer.k21]

@NARAYANK991 @Deepak any suggestions?

 

[PCosta87]

Hi @Sameer.k21

You can use the following code to identify the last row + 1. Just replace columns("F") with any column containing the pivot tables:

Columns("F").Cells(Rows.Count).End(xlUp).Row + 1

Hope this helps

 

[Sameer.k21]

Hi @Sameer.k21

You can use the following code to identify the last row + 1. Just replace columns("F") with any column containing the pivot tables:

Columns("F").Cells(Rows.Count).End(xlUp).Row + 1

Hope this helps

Thanks @PCosta87
I used

LastRow = PSheet.Cells(Rows.Count, 1).End(xlUp).Row

Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(LastRow + 4, 1), TableName:="LangPivotTable4")

 

[PCosta87]

@Sameer.k21

You're welcome

 

[Sameer.k21]

@PCosta87 any suggestions on filtering xlColumnField and selecting one value?

 

[PCosta87]

@PCosta87 any suggestions on filtering xlColumnField and selecting one value?

Untested but you should be able to use something like:

 Sheets(1).PivotTables("LangPivotTable4").PivotFields("C").PivotFilters.Add2 _
        Type:=xlCaptionEquals, Value1:="c2"

... by replacing PivotFields("C") with the field you wish to apply the filter to, and Value1:="C2" with the criteria for the filter

If you have any further questions just ask

 

[Sameer.k21]

Thank you so much. This is exactly what I was trying. Why is Value1 and not just Value?

 

[PCosta87]

Thank you so much. This is exactly what I was trying. Why is Value1 and not just Value?

@Sameer.k21

You are welcome

About your question, it is value1 because you can have criteria with more than one value... for instance, if you wanted to display data between two dates you would have to use value1 and value2, start date and end date, respectively.

 

[Sameer.k21]

Thanks again!! you explained really well. This helped a lot