Source data PVT Tables VBA

thefrasers

Board Regular
Joined
Aug 29, 2007
Messages
58
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...

Rich (BB code):
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...

Rich (BB 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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

pcc

Well-known Member
Joined
Jan 21, 2003
Messages
1,345
you have unbalanced braces in this


Code:
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Rich (BB code):
  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

Board Regular
Joined
Aug 29, 2007
Messages
58
Good catch but not the problem.
Code:
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

Well-known Member
Joined
Jan 21, 2003
Messages
1,345

ADVERTISEMENT

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


Selection.Address for the range
 

pcc

Well-known Member
Joined
Jan 21, 2003
Messages
1,345
ie try
Code:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Selection.Address).CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable5", DefaultVersion:=xlPivotTableVersion10
 

thefrasers

Board Regular
Joined
Aug 29, 2007
Messages
58

ADVERTISEMENT

Sorry
Code:
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
 

thefrasers

Board Regular
Joined
Aug 29, 2007
Messages
58
YES!!!!! Thank you very much. Apologies for being dumb with brackets.
Cheers
Peter
 

pcc

Well-known Member
Joined
Jan 21, 2003
Messages
1,345
Easily done - glad to have been of help....
 

Watch MrExcel Video

Forum statistics

Threads
1,127,348
Messages
5,624,145
Members
416,014
Latest member
MickP69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top