| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting cells to produce a pivot table
I have created a macro that inserts a pivot table using some data on sheet1. I thought that the macro would select the entire list of data to produce the pivot each time it is run but I notice in the code that it selects an absolute range and the range will be different each time I run the macro. How can I change the code to tell Excel to select the populated cells in the list from A1 to the end? Using v2003 - thanks PS - I also made a mistake recording the macro and meant for one of the value fields in the pivot table to perform a sum rather than a count is this easy to amend using code? Anita |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting cells to produce a pivot table
Anita,
Post your code - the changes required depend on the specifics of your current code. HTH, Bernie MS Excel MVP "Anita" wrote in message ... I have created a macro that inserts a pivot table using some data on sheet1. I thought that the macro would select the entire list of data to produce the pivot each time it is run but I notice in the code that it selects an absolute range and the range will be different each time I run the macro. How can I change the code to tell Excel to select the populated cells in the list from A1 to the end? Using v2003 - thanks PS - I also made a mistake recording the macro and meant for one of the value fields in the pivot table to perform a sum rather than a count is this easy to amend using code? Anita |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting cells to produce a pivot table
Here's the code thanks...
Rows("1:6").Select Selection.Delete Shift:=xlUp Columns("A:A").Select Selection.Delete Shift:=xlToLeft Columns("H:H").Select Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Order older than inv?" Range("H2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]RC[-5],1,0)" Selection.AutoFill Destination:=Range("H2:H5570") Range("H2:H5570").Select Range("G2").Select ActiveWindow.LargeScroll ToRight:=1 Range("L1").Select ActiveCell.FormulaR1C1 = "Cost Centre" Range("M1").Select ActiveCell.FormulaR1C1 = "Division" Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R5570C13").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable2", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _ "Cost Centre", "Division", "Supplier Title", "Data") With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Invoice Number") .Orientation = xlDataField .Position = 1 End With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Order older than inv?"). _ Orientation = xlDataField End Sub "Bernie Deitrick" wrote: Anita, Post your code - the changes required depend on the specifics of your current code. HTH, Bernie MS Excel MVP "Anita" wrote in message ... I have created a macro that inserts a pivot table using some data on sheet1. I thought that the macro would select the entire list of data to produce the pivot each time it is run but I notice in the code that it selects an absolute range and the range will be different each time I run the macro. How can I change the code to tell Excel to select the populated cells in the list from A1 to the end? Using v2003 - thanks PS - I also made a mistake recording the macro and meant for one of the value fields in the pivot table to perform a sum rather than a count is this easy to amend using code? Anita . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting cells to produce a pivot table
Anita,
Change ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R5570C13").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable2", DefaultVersion:=xlPivotTableVersion10 to ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R" & Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row _ & "C13").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable3", DefaultVersion:=xlPivotTableVersion10 HTH, Bernie MS Excel MVP "Anita" wrote in message ... Here's the code thanks... Rows("1:6").Select Selection.Delete Shift:=xlUp Columns("A:A").Select Selection.Delete Shift:=xlToLeft Columns("H:H").Select Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Order older than inv?" Range("H2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]RC[-5],1,0)" Selection.AutoFill Destination:=Range("H2:H5570") Range("H2:H5570").Select Range("G2").Select ActiveWindow.LargeScroll ToRight:=1 Range("L1").Select ActiveCell.FormulaR1C1 = "Cost Centre" Range("M1").Select ActiveCell.FormulaR1C1 = "Division" Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R5570C13").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable2", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _ "Cost Centre", "Division", "Supplier Title", "Data") With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Invoice Number") .Orientation = xlDataField .Position = 1 End With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Order older than inv?"). _ Orientation = xlDataField End Sub "Bernie Deitrick" wrote: Anita, Post your code - the changes required depend on the specifics of your current code. HTH, Bernie MS Excel MVP "Anita" wrote in message ... I have created a macro that inserts a pivot table using some data on sheet1. I thought that the macro would select the entire list of data to produce the pivot each time it is run but I notice in the code that it selects an absolute range and the range will be different each time I run the macro. How can I change the code to tell Excel to select the populated cells in the list from A1 to the end? Using v2003 - thanks PS - I also made a mistake recording the macro and meant for one of the value fields in the pivot table to perform a sum rather than a count is this easy to amend using code? Anita . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting cells to produce a pivot table
Thanks v much for your help - I'll give this a try when I'm next in work and
have access to some raw data to test it... "Bernie Deitrick" wrote: Anita, Change ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R5570C13").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable2", DefaultVersion:=xlPivotTableVersion10 to ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R" & Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row _ & "C13").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable3", DefaultVersion:=xlPivotTableVersion10 HTH, Bernie MS Excel MVP "Anita" wrote in message ... Here's the code thanks... Rows("1:6").Select Selection.Delete Shift:=xlUp Columns("A:A").Select Selection.Delete Shift:=xlToLeft Columns("H:H").Select Selection.Insert Shift:=xlToRight Range("H1").Select ActiveCell.FormulaR1C1 = "Order older than inv?" Range("H2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]RC[-5],1,0)" Selection.AutoFill Destination:=Range("H2:H5570") Range("H2:H5570").Select Range("G2").Select ActiveWindow.LargeScroll ToRight:=1 Range("L1").Select ActiveCell.FormulaR1C1 = "Cost Centre" Range("M1").Select ActiveCell.FormulaR1C1 = "Division" Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R5570C13").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable2", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _ "Cost Centre", "Division", "Supplier Title", "Data") With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Invoice Number") .Orientation = xlDataField .Position = 1 End With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Order older than inv?"). _ Orientation = xlDataField End Sub "Bernie Deitrick" wrote: Anita, Post your code - the changes required depend on the specifics of your current code. HTH, Bernie MS Excel MVP "Anita" wrote in message ... I have created a macro that inserts a pivot table using some data on sheet1. I thought that the macro would select the entire list of data to produce the pivot each time it is run but I notice in the code that it selects an absolute range and the range will be different each time I run the macro. How can I change the code to tell Excel to select the populated cells in the list from A1 to the end? Using v2003 - thanks PS - I also made a mistake recording the macro and meant for one of the value fields in the pivot table to perform a sum rather than a count is this easy to amend using code? Anita . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How I can create a Pivot table by selecting two Different Pivot | Excel Worksheet Functions | |||
| Pivot Table Programming (Selecting Pivot Items) | Excel Programming | |||
| Pivot Table Programing Help (Selecting Pivot Items) | Excel Programming | |||
| selecting a pivot table through VBA | Excel Programming | |||
| Selecting Rows in Pivot Table | Excel Programming | |||
Hybrid Mode
