Dynamic Array with non-worksheet-specific cell references

[SMiller16]

Hi all,

In Excel 2010, Windows 7, I've created a macro for a pivot table with a dynamic array as source data. I created the array by naming a table with the formula =OFFSET(a1,0,0,COUNTA(a:a),3), so that I could always incorporate new rows of data into the macro each time I run it.

The problem is, when I try to run this macro from tables on different worksheets, it will always call up the original table from the first worksheet. I was able to replace "sheet1" with activecell in order to make the pivot table appear in the specified cell on the new sheet, but I haven't been able to figure out how to make the macro re-run the OFFSET function on the activesheet each time.

The following is the macro I've come up with, and the named table I'm using as my source data is "FruitRange":

Sub Macro6()
'
' Macro6 Macro
'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"FruitRange", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="R1C10", TableName:="PivotTable3", DefaultVersion _
:=xlPivotTableVersion14
ActiveSheet.Select
Cells(1, 10).Select
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWindow.Zoom = 85
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Product")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Sales Rep")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Sales"), "Sum of Sales", xlSum
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Range("K1").Select
ActiveSheet.PivotTables("PivotTable3").CompactLayoutColumnHeader = "Product"
Range("J2").Select
ActiveSheet.PivotTables("PivotTable3").CompactLayoutRowHeader = "Sales Rep"
Range("K3").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable3").TableStyle2 = "PivotStyleLight15"
ActiveSheet.PivotTables("PivotTable3").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("PivotTable3").ShowTableStyleRowHeaders = False
ActiveSheet.PivotTables("PivotTable3").ShowTableStyleColumnHeaders = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("J12").Select
End Sub


Thank you so much for your help!! Any little bit of direction is greatly appreciated!! I only began learning macros/VBA a few days ago, so I know I have a long way to go!!

Sarah

 

[Marcelo Branco]

Hi SMiller,

Welcome to the forum!

Maybe you can create the range (SourceData) inside the macro.

Seeing your dynamic range FruitRange it seems that its located at columns A B C beginning in A1. Am i right?

If so maybe something like this (try it on a test workbook)

Sub Macro6()
    Dim PTcache As PivotCache, PT As PivotTable
    Dim rng As Range
 
    With ActiveSheet
        'Get the range in columns A B C
        Set rng = .Range("A1:C" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
 
     ' Create a PivotCache
    Set PTcache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=rng)
 
     'Create the PivotTable from the Cache
    Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTcache, _
        TableDestination:=Range("J4"), TableName:="PivotTable3")
 
     With PT
           ' Add your Fields here, define Style, etc and finish the code as you want
 
           ' Format Table
            .ShowDrillIndicators = False
            .TableStyle2 = "PivotStyleMedium9"
 
     End With
 
End Sub

HTH

M.

 

[SMiller16]

It worked! You are a life-saver, thank you!!

 

[Marcelo Branco]

It worked! You are a life-saver, thank you!!

You are welcome and tks for the feedback

M.