Source data PVT Tables VBA

[thefrasers]

I apologise if this seems like more duplication, but I rather suspect that my initial message had been buried beneath the more recent enquiries. Usually I'm answered in minutes so I'm guessing that people only look at the initial stuff.

My problem is straightforward (I think):

I previously had a routine using subtotals but yesterday I discovered pivot tables, which are much neater. So, I am trying to write a macro which builds multiple pivot tables from multiple sets of data. After each pivot table is built and moved to a named sheet, the macro shifts to look at new data and builds a new pivot table for a new sheet.

In theory.

I am OK naming the sheets, but the same pivot table keeps reappearing in every sheet because the source data doesn't seem to change. At the moment the code (built as usual using the macro recorder) looks like this...

Dim cell As Range
Dim y As Integer
     
    y = 13

For Each cell In Range("CCOMP")
    Sheets("Master").Select
    Range(Cells(8, y), Cells(200, y + 1)).Select
    Range(Selection, Selection.End(xlDown)).Select

(I thought that by re-selecting a range each time that the pivot table data would also change.
I guess it doesn't).

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Master!R8C13:R200C15").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable5", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Counterparty")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Trade"), "Sum of Trade", xlSum
     ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
  ActiveSheet.Name = ThisWorkbook.Worksheets("master").Cells(4, y).Value

y = y + 3
 Next cell

which only gives one hard wired range for the pivot table. I would like source data to adjust as follows:

SourceData:=Sheets("Master").Range(Cells(8, y), Cells(200, y + 1)

but if I input the following new code...

   ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= 
        Sheets("Master").Range(Cells(8, y), Cells(200, y + 1).CreatePivotTable 

TableDestination:="", TableName:= "PivotTable5", DefaultVersion:=xlPivotTableVersion10

... it turns red and tells me I have a compile error. it highlights the "TableDestination:=""" which may mean something but I have no idea what this element does.

This seems like a relatively straightforward problem but it is doing me in.

can anyone help?

Cheers,
Peter

 

[pcc]

you have unbalanced braces in this

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= 
        Sheets("Master").Range(Cells(8, y), Cells(200, y + 1).CreatePivotTable 

TableDestination:="", TableName:= "PivotTable5", DefaultVersion:=xlPivotTableVersion10

try adding another ")" before the ".CreatePivotTable

 

[Richard Schollar]

Hello Peter

You need to tell the procedure where to place the Pivot Table. also, you are using unqualified Cells() references which may cause you problems. try replacing with:

  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= 
        Sheets("Master").Range(Sheets("Master").Cells(8, y), Sheets("Master").Cells(200, y + 1))).CreatePivotTable 
 
TableDestination:=Sheets("SomeSheet").Range("A1"), DefaultVersion:=xlPivotTableVersion10

Replace the elements in Red with whatever is appropriate.

 

[thefrasers]

Good catch but not the problem.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Sheets("Master").Range(Cells(8, y), Cells(200, y + 1)).CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable5", DefaultVersion:=xlPivotTableVersion10

still has same error.
Peter

 

[pcc]

sorry i think you need two )) to balnce it.
Alternatively, try


Selection.Address for the range

 

[pcc]

ie try

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Selection.Address).CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable5", DefaultVersion:=xlPivotTableVersion10

 

[thefrasers]

Sorry

Sheets("Master").Select
    Range(Cells(8, y), Cells(200, y + 1)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        selection.address.CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable5", DefaultVersion:=xlPivotTableVersion10

Still going red and giving me a syntax error.

Richard what are unqualified cell references?

Peter

 

[pcc]

you missed a ) again

 

[thefrasers]

YES!!!!! Thank you very much. Apologies for being dumb with brackets.
Cheers
Peter

 

[pcc]

Easily done - glad to have been of help....