Inserting a Pivot Table a few rows below a dynamic Range

[Yeft]

Hello , Any one who could help me with a macro for inserting my pivot Table a few rows below a Dynamic Range named "My Range"?
My data has 8 Columns and Headers are in Row 2. Pivot Table is to be placed in the same sheet , but under my dynamic Range.
Any help is much appreciated.
Thanks!

 

[Kaper]

Probably you would insert your Pivot table in given cell of the sheet wit command similar to (for placement in J20 (so column 10 row 20):

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R2C10:R11C12", Version:=8).CreatePivotTable TableDestination:= _
        "Sheet1!R20C10", TableName:="PivotTable1", DefaultVersion:=8

But you want it in a specific place, let's say 5 rows below your dynamic range MyRange (spaces are not allowed in range names I think)
you shall calculate how many rows is there in MyRAnge, for instance like:

dim rowsinnamed as long
rowsinnamed = UBound(Sheets("Sheet1").Evaluate("MyRange"))

let's say named range starts in row 15
then code shall be:

dim rowsinnamed as long
rowsinnamed = UBound(Sheets("Sheet1").Evaluate("MyRange"))
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R2C10:R11C12", Version:=8).CreatePivotTable TableDestination:= _
        "Sheet1!R" & 15 +rowsinnamed + 5 +1 & "C10", TableName:="PivotTable1", DefaultVersion:=8

so starting row + rowsinnamed + empty rows below + 1 for next row (not last of the above sum).Let us know if that works for you

 

[Yeft]

Hello kaper,

It did work perfectly!, Thank you so much!