Creating Pivot Tables using VBA

[Chissa]

Hi there,

I am trying to create a pivot table using excel data from another sheet, this is my code so far but when I run it, it has the following error message:

Run-Time Error '1004'
The pivot table field name is not valid. To create a pivottable report, you must use the data that is organised as a list with labelled columns. If you are changing the name of a pivot table field, you must type a new name for the field.

This is my code, I have highlight the bit that brings up the error:
Sub PivotTable()
Dim StrGLPivotTable As String
StrGLPivotTable = ("Pivot Table Analysis")
Dim WKSData As Worksheet
Set WKSData = ThisWorkbook.Worksheets("Trial Balance Comparison")
Dim RngData As Range
Set RngData = WKSData.UsedRange
Sheets.Add after:=ActiveSheet 'adding a new sheet
ActiveSheet.Name = StrGLPivotTable
Dim WKSDestination As Worksheet
Set WKSDestination = Sheets(StrGLPivotTable)
Dim PvtTable As PivotTable
Set PvtTable = WKSData.PivotTableWizard(SourceType:=xlDatabase, _
SourceData:=RngData, TableDestination:=ActiveSheet.Cells(1, 4))

Hope you can help.

Thank you

 

[pcc]

the used range of your source data probably has at least one column without a header

 

[Chissa]

Sorry I seem to be really struggling with implementing a pivot table through VBA.
Basically I have a data sheet in the workbook called "Trial Balance Comparison", I would like to know how to get the Pivot table on to a new worksheet that I have created called "Pivot Table Analysis".

The pivot table fields I require are:
"G/L Account Number" xl RowField
"Total in (GBP)" , xlSum

Please can somebody help me write the code? Or give me some clue.

Thank you

 

[Weaver]

Going back to basics, have you tried creating the pivot table yourself whilst recording macros?

 

[Chissa]

Thank you for your suggestion, but yes I have tried using the Marco and various other resources to try and work it out on my own. I have got as far as this,the part I have colour coded red is the bit where it gets stuck the error message says "Compile Error: Expected:end of statement:

Dim StrGLPivotTable As String
StrGLPivotTable = ("Pivot Table Analysis")
Dim WKSData As Worksheet
Set WKSData = ThisWorkbook.Worksheets("Trial Balance Comparison")
Dim RngData As Range
Set RngData = Worksheets("Trial Balance Comparison").Range("A5:F" & LastRow9)
Sheets.Add after:=ActiveSheet 'adding a new sheet
ActiveSheet.Name = StrGLPivotTable 'Renaming the Sheet "TB Comparison
Dim WKSDestination As Worksheet
Set WKSDestination = Sheets(StrGLPivotTable)
Dim PvtTable As PivotTable
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="'Trial Balance Comparison'!R4C1:R65536C6") _
.CreatePivotTable TableDestination:= Activesheets "'Pivot Table Analysis'!R4C1",TableName:="TB Comparison"

With ActiveSheet.PivotTables("PivotTable1")
.PivotFields ("G/L Account Number")
.Orientation = xlRowField
.Position = 1
With .PivotFields("Total in (GBP)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "£#,##0.00"
End With
End With

End Sub

 

[Weaver]

try changing to

.CreatePivotTable TableDestination:= sheets("Pivot Table Analysis").range("A4"),TableName:="TB Comparison"

What version of xl are you using?

 

[Chissa]

I am using Excel 2003, do you think it VBA is better on 2010 or no difference?

Thank you very much!!!! That has helped....have got the pivot table in there, just now brought up an error with the field items:

With ActiveSheet.PivotTables("TB Comparison")
.PivotFields ("G/L Account Number")
.Orientation = xlRowField
.Position = 1
With .PivotFields("Total in (GBP)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "£#,##0.00"
End With
End With

Error reads: Run-time error '438' Object doesn't support this property or method

 

[taurean]

Untested:

With ActiveSheet.PivotTables("TB Comparison").PivotFields ("G/L Account Number")
[COLOR=#ff0000][COLOR=black].Orientation = xlRowField[/COLOR]
[/COLOR].Position = 1
End With
 
With ActiveSheet.PivotTables("TB Comparison").PivotFields("Total in (GBP)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "£#,##0.00"
End With

 

[Chissa]

Thank you all for your help! You are brilliant!!

 

[Weaver]

I think what you had in mind was

With ActiveSheet.PivotTables("TB Comparison")
  with .PivotFields ("G/L Account Number")
    .Orientation = xlRowField
    .Position = 1
  End With
  With .PivotFields("Total in (GBP)")
    .Orientation = xlDataField
    .Function = xlSum
    .NumberFormat = "£#,##0.00"
  End With
End With

But it looks like you got there anyway.