• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

trying to create pivot table from vba code

V

vandanarana

New Member
thank you shrivallbha for your help.


i have tried your code with quotes its giving me

run time error 1004


application defined or object defined error
 
Hi Vandana,


Welcome To Chandoo_Org.


Your code has got an error handler in place so it will not tell you where the error is coming at run time. So comment it out first and see where the actual error is. See the commented out code:

[pre]
Code: 
Public Sub CreateCompletePivotTable()
Dim wb As Workbook
Dim pt As PivotTable

'On Error GoTo errorhandler
'open the workbook.
Set wb = Workbooks.Open("c:vandanaadvptexamplesVideoStoreRawData.xls")

'create the pivottable and get a reference to it.
Set pt = Worksheets("Sheet1").PivotTableWizard(SourceType:=xlDatabase, SourceData:=Range("Sheet1!A4:C28"), tabledestination:=Range("Sheet2!F2"))

' Add row and column fields
pt.AddFields RowFields:="Store", ColumnFields:="Category"

'Add data Field.
pt.AddDataField pt.PivotFields("Titles"), "Total Titles"

'endofsub:

'Exit Sub

'errorhandler:

'If Err.Number = 5 Or Err.Number = 9 Then
'MsgBox "The file could not be found"

'ElseIf Err.Number = 1004 Then
'MsgBox "There is already a pivot table on that location"

'Else
'MsgBox "Error" & Err & " - " & Err.Description
'End If

'Resume endofsub
End Sub
[/pre]
Step through this code and see where it falters and then get back to us.
 
hi shrivallabha,


thank u very much for help but the following code is giving me run time error 1004

application defined or object defined error.


Public Sub CreateCompletePivotTable()

Dim wb As Workbook

Dim pt As PivotTable


'On Error GoTo errorhandler

'open the workbook.

Set wb = Workbooks.Open("c:vandanaadvptexamplesVideoStoreRawData.xls")


'create the pivottable and get a reference to it.

Set pt = Worksheets("Sheet1").PivotTableWizard(SourceType:=xlDatabase, SourceData:=Range("Sheet1!A4:C28"), tabledestination:=Range("Sheet2!F2"))


' Add row and column fields

pt.AddFields RowFields:="Store", ColumnFields:="Category"


'Add data Field.

pt.AddDataField pt.PivotFields("Titles"), "Total Titles"


'endofsub:


'Exit Sub


'errorhandler:


'If Err.Number = 5 Or Err.Number = 9 Then

'MsgBox "The file could not be found"


'ElseIf Err.Number = 1004 Then

'MsgBox "There is already a pivot table on that location"


'Else

'MsgBox "Error" & Err & " - " & Err.Description

'End If


'Resume endofsub

End Sub
 
Set pt = Worksheets("Sheet1").PivotTableWizard(SourceType:=xlDatabase, SourceData:=Range("Sheet1!A1:C25"), tabledestination:=Range("Sheet1!F2"))


hi, on the above line its giving error but it is able to open the videostorerawdata file. its opening that file.
 
Hi,


I guess for the range A1:C25 (cells A1,B1,& C1 )should have a header (It should not be blank).


Thanks,

Suresh Kumar S
 
Hi Vandana,


This is lightly tested code (without any error handler). See if this works as intended in the first place.

[pre]
Code: 
Sub CreatePivotTable()
Dim wb As Workbook
Dim pc As PivotCache
Dim pt As PivotTable
'Open Workbook
Set wb = Workbooks.Open(Filename:="C:vandanaadvptexamplesVideoStoreRawData.xls")
'Create Pivot Cache first
Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("Sheet1!A4:C28"))
'Create Pivot Table
Set pt = pc.CreatePivotTable(TableDestination:=Range("Sheet2!F2"), TableName:="PivotTable2")
With pt 'Work out layout
With .PivotFields("Store")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Category")
.Orientation = xlColumnField
.Position = 1
End With
.AddDataField .PivotFields("Titles"), "Sum of Titles", xlSum
End With
End Sub
[/pre]
 
runtime error 5

it is giving error invalid procedure call or argument at with pt in your code after


Set pt = pc.CreatePivotTable(TableDestination:=Range("Sheet1!F2"), TableName:="PivotTable2")


thanks alot for ur help,

but still not working.

see you tomorrow.

bye.
 
I did not get this error. I tried to recreate the error and got it when I change

following line:

Code: 
Set pt = pc.CreatePivotTable(TableDestination:=Range("Sheet2!F2"), TableName:="PivotTable2")

to

Set pt = pc.CreatePivotTable(TableDestination:="Sheet2!F2", TableName:="PivotTable2")

i.e. remove the range keyword in TableDestination argument.


Maybe we should check what your macro recorder gets. Tomorrow, do the following:

1. Open a blank workbook.

2. Start macro recorder.

3. Do above steps for creating pivot table manually but precisely as they are above.

4. Post the code from the recorded macro here.


I'll take a look at it in the evening.
 
vandanarana,

What I have found to be a lot of help when I'm having trouble with some VBA, is that just turn on the recorder and do whatever I'm trying to do ( assuming it can be done via the recorder). Of course it won't be perfect, but it will give you a good head start of what needs to be done next. I've always had trouble just copying and pasting someone else's code and fully understanding it.
 
Thank you very much Mr. Nick for your kind advice. but you know that in excel 2007 macros are not getting recorded. that is why i am trying with VBA Code. i know that i am new to VBA and trying to get some code working for creating pivot tables. For your information i want to tell you that i got VBA code working for creating pivot tables.


Anyhow, thanks alot for your help and advice,


Thanking you,

Vandanarana
 
Macros do get recorded in Version 2007 but the caveat is: not all that would have been recorded with 2003 get recorded in 2007. Did you try to record this macro as I suggested?


Edit:= I see that you were able to create pivot using VBA. Good that you were able to solve it. Please post your final code for the benefit of people who might search this thread in future.
 
You must log in or register to reply here.
Back
Top