Creating a pivot table using a macro

[vbsteve]

 

[Dirt]

This macro assumes you have the data to be charted on a sheet named "Data" and that the first data point is in cell A1. If not, the compile will fail.

 

[MacroCrazy]

I've using the code posted, and it's been working for months. However, I am now getting the Run-time error'13': Type mismatch message. It is pointing at: (Please help)

Set PtCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=s.Range("Items"))

Here's other parts of my code:
'declare the row, column, page and data field variables
Dim Pt As PivotTable
Dim PtCache As PivotCache
Dim pageField1 As String
Dim pageField2 As String
Dim pageField3 As String
Dim rowField1 As String
Dim rowField2 As String
Dim colField As String
Dim dataField As String
'delete "report" if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Report").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'
Set s = Sheets("Data")
With Worksheets.Add
.Name = "Report"
End With

'Pass variable names to a String variable
pageField1 = s.Cells(1, 2).Value 'page variable "Dispo code"
rowField1 = s.Cells(1, 3).Value 'row field "Op No"

colField1 = s.Cells(1, 10).Value 'colums field "Defect"
dataField = s.Cells(1, 11).Value 'data to summarize "Tons"
'Name the list range
Worksheets("Data").Activate
ActiveSheet.Range("a1").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "Items"
'create pivot cache

Set PtCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=s.Range("Items"))
'entire contents of sheet

'create pivot table from cache
Set Pt = PtCache.CreatePivotTable( _
TableDestination:=Sheets("Report").Range("A1"), _
TableName:="QualCodeTable")

 

[JFCJ]

Try changing this line:

SourceData:=s.Range("Items"))

To

SourceData:=s.Range("Items").Address(ReferenceStyle:=xlR1C1))

 

[Nishchint2006]

For some reason the text has appeared on my first post....

I have recorded a macro while creating a pivot table but the range that the original table was created from will change from week to week. I need to write the macro to select the current range on the sheet the table is being created from!

Can anyone tell me how to do this????

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        "'Prior To Pivot Table'!R2C1:R1194C3", TableDestination:="", TableName:= _
        "PivotTable2"
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("Proj_only" _
        , "Data")
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("GL WIP")
        .Orientation = xlDataField
        .Name = "Sum of GL WIP"
        .Position = 1
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Con Reg WIP")
        .Orientation = xlDataField
        .Name = "Sum of Con Reg WIP"
        .Function = xlSum
    End With
End Sub

'Dynamic range can be specified as below

1. Name the Range

Choose Insert>Name>Define
Type a name for the range, e.g. Database
In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in a column that doesn't contain any blank cells. , e.g.:
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7)
In this example, the list is on a sheet named 'Data', starting in cell A1. The arguments used in this Offset function are:
Reference cell: Data!$A$1
Rows to offset: 0
Columns to offset: 0
Number of Rows: COUNTA(Data!$A:$A)
Number of Columns: 7
Note: for a dynamic number of columns,
replace the 7 with: COUNTA(Data!$1:$1)
Click OK
2. Base the Pivot Table on the Named Range

Select a cell in the database
Choose Data>PivotTable and PivotChart Report
Select 'Microsoft Excel List or Database', click Next.
For the range, type your range name, e.g. Database
Click Next
Click the Layout button
Drag field buttons to the row, column and data areas
Click OK, click Finish

 

[dwhite30518]

I have a workbook with multiple worksheets. Each worksheet has a varying number of cells. I am trying to create a macro that will create a pivot table for each of these worksheet as well as any new worksheet I add to the workbook. I tried using the "Record Macro" feature, but when I run the macro on the next sheet, I get either a de-bug error or a Run-time error '1004'. The code recorded is as follows...

Sub RSA()
'
' RSA Macro
'
' Keyboard Shortcut: Ctrl+w
'
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("A1:C201").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"9389397!R1C1:R201C3", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="9389397!R1C6", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion14
Sheets("9389397").Select
Cells(1, 6).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("ItemNumber")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("SerialNumber"), "Count of SerialNumber", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("I9").Select
End Sub


I want to make sure this code will be able to account for the different quantity of rows I currently have and will come across in the future.

Can anyone help shed some light on this error and how to resolve it and to make sure it includes all populated rows?

Thanks!!!!

 

[stan1989]

hi all. dont mean to hijack but i was going through this thread and used 1 of the suggested code.

Sub MakeTable()
'declare the row, column, page and data field variables
Dim Pt As PivotTable
Dim PtCache As PivotCache
Dim pageField1 As String
Dim pageField2 As String
Dim pageField3 As String
Dim rowField1 As String
Dim rowField2 As String
Dim colField As String
Dim dataField As String

'delete sorted sheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Sorted").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'
Set s = Sheets("Combine Sheet")
With Worksheets.Add
    .Name = "Sorted"
End With
    
    'Pass variable names to a String variable
    'pageField1 = s.Cells(1, 2).Value  'page variable "Dispo code"
    rowField1 = s.Cells(1, 3).Value  'row field "CUST NAME"
    rowField2 = s.Cells(1, 4).Value  'row field "PRODUCT"
    
    'colField1 = s.Cells(1, 10).Value  'colums field "Defect"
    dataField = s.Cells(1, 5).Value  'data to summarize "Tons"
'Name the list range
ActiveWorkbook.Worksheets("Combine Sheet").Activate
ActiveSheet.Range("C1").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "Items"
'create pivot cache
Set PtCache = ActiveWorkbook.PivotCaches.Add( _
    SourceType:=xlDatabase, _
    SourceData:=s.Range("Items")) 'entire contents of sheet
    
'create pivot table from cache
Set Pt = PtCache.CreatePivotTable( _
    TableDestination:=Sheets("Sorted").Range("A1"), _
    TableName:="PivotTable1")
    
'add fields
With Pt
    .PivotFields(rowField1).Orientation = xlRowField   'Cust Name
    .PivotFields(rowField1).Position = 1
    .PivotFields(rowField2).Orientation = xlRowField   'Product
    .PivotFields(rowField2).Position = 2
    '.PivotFields(pageField1).Orientation = xlPageField  'DID NOT USE
    '.PivotFields(colField1).Orientation = xlColumnField  'DID NOT USE
    .PivotFields(dataField).Orientation = xlDataField    'Revenue
    .PivotFields(dataField).Caption = "Sum of Final Total Revenue (EUR)" 'Using to sum $$
    .PivotFields(dataField).Function = xlSum 'to sum instead of count
   
    'With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    '    "Count of Final Total Revenue (EUR)")
    '    .Caption = "Sum of Final Total Revenue (EUR)"
    '    .Function = xlSum
    
End With

    'Pt.PivotFields.xlDataField.Function = xlSum


ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSTOMER NAME IFX"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
        
Worksheets("Sorted").CurrentRegion.AutoFit
Application.ScreenUpdating = True
    
End Sub

i want the code to sum up the data field of revenue instead of counting the number of occurences. but ' .PivotFields(dataField).Function = xlSum ' doesnt really seem to cut it as it always gives me a compiling error. can i get some help on this?

many thanks in advance!!

 

[excel_1317]

Try changing this line:

SourceData:=s.Range("Items"))

To

SourceData:=s.Range("Items").Address(ReferenceStyle:=xlR1C1))

I was geeting the same error and changed the code line as you have mentioned. But now I am getting a Run time error 1004 on line..

.PivotFields(colField1).Orientation = xlColumnField

Could you please suggest on what needs to be done? Thanks

 

[miva0601]

Can someone please help me debug the code in red? I was following Bill Jelen's Youtube tutorial episode 1211 on how to fix the hard coded parts of the macro -- the fact that pivot table went to new work sheet and that new sheet is called ".." -- I need to fix this hard coded part, as well as need Data Sheet as a variable.

Please help! I use the record macro function, and only need these two variables fixed. Thank you!

COPY / PASTED directly from VBA:

Sub CreatePivot()
'
' CreatePivot Macro
'
' Keyboard Shortcut: Ctrl+r
'
DataSheet = ActiveSheet.Name
Sheets.Add
NewSheet = ActiveSheet.Name

Range("A4").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Range("A4").Select
Selection.AutoFill Destination:=Range("A4:Z4"), Type:=xlFillDefault
Range("A4:Z4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:3").Select
Range("A3").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Cells.Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
DataSheet & "!R1C1:R1048576C26", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=NewSheet & "!R3C1", TableName:="PivotTable3", DefaultVersion _
:=xlPivotTableVersion14
Sheets(NewSheet).Select
Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Order/ship reference")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Item")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Received")
.Orientation = xlRowField
.Position = 3
End With