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...
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...
... 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
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