Teach Yourself Excel Lesson Create A Pivot Table - Create A Pivot Table

I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation. Please advise. Code:

Sub Step_5ab()

 Dim DstWkb As Workbook
  Dim SrcWkb As Workbook
  Dim Rng As Range, Dn As Range, n As Integer
  Dim RngEnd As Range
  Dim Pt As PivotTable
  Dim strField As String

  Set Rng = Selection
  
  With Application
        .ScreenUpdating = False
        .EnableEvents = False
  End With
    
    Set DstWkb = Workbooks("NewDirection.xls")
    Set SrcWkb = Workbooks("ESS.xls")
    
    
    DstWkb.Activate
        
  'Copy and paste the Procedure - Okay
    Sheets("Sheet1").Range("A254").EntireRow.Copy
    Sheets("Results").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
  SrcWkb.Worksheets("FY 10 by PAC").Activate

    'Copy and paste all of the ESS data -Okay
        Range("A1:N144").Copy _
                Destination:=DstWkb.Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0)
          
   'Copy and paste a blank row - Okay
     DstWkb.Worksheets("Sheet1").Range("A272").Copy
     DstWkb.Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'Create pivot for APR Data-Here's the problem
    
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "APRData!C1:C23").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("PAC", _
        "Job Title", "Unit")
    ActiveSheet.PivotTables("PivotTable1").PivotFields("FTE").Orientation = _
        xlDataField
    ActiveWorkbook.ShowPivotTableFieldList = True
    Range("C4").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Unit")
        .Orientation = xlColumnField
        .Position = 1
    End With

    
    'Label, copy, and paste
    
     'Copy only the filtered data
           Range("A1").Activate
        Rng.SpecialCells(xlCellTypeVisible).Copy _
                Destination:=DstWkb.Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0)
                
              
    'Copy and paste the Solution
        Sheets("Sheet1").Range("A265").EntireRow.Copy
        Sheets("Results").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    'Copy two blank rows
     Sheets("Sheet1").Range("A272:A273").Copy
     Sheets("Results").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
          
        'Alert the user on progress
        MsgBox "Step 5 Compare FTE Position totals to ESS is complete; the results have been recorded"
          Run "Step_6a"

End Sub

Hi,

I have a Workbook called "Sales.xls" located in c:\ and an access data base located in the same path with the name "Totals.mdb". In this data base I've a table named "Retail". My problem is that I want to create a Pivot Table in Excel with the whole table "Retail" in vba. I found a code on this page and tried to adapt to my case.
The reference is:
Code:

http://www.java2s.com/Code/VBA-Excel-Access-Word/Excel/CreatePivotTableFromdatabase.htm

And my code is like this:
Code:

Sub CreatePivotTableFromDB()
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim DBFILE As String  
   
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    
    DBFILE = ThisWorkbook.Path & "\Totals.mdb"
    ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFILE
    QueryString = "SELECT * FROM '" & ThisWorkbook.Path & "\Totals'.Retail Retail"
    
    With PTCache
        .Connection = ConString
        .CommandText = QueryString
    End With
    
    Worksheets.Add
    ActiveSheet.Name = "PivotSheet"

    
    Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"), TableName:="BudgetPivot")
     
End Sub

The code breaks when it has to do Set PT, it says the following error:

Run-time-error '1004'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in query.
Incomplete query clause

Someone has any idea of what is breaking the code or any other alternative to do my request, but it is important to say that i don't want to import the data into a worksheet.

Thanks,

All,

I am creating a Pivot table in Excel using vbscript and I get the below error "Invalid Procedure call" while running the below script:

Set xlptCache = xlBook.PivotCaches
With xlptCache.Add(2)
.Connection = "OLEDB;Provider=MSOLAP.3;Data Source=MT000XSRPD91;Initial Catalog=ICE_CUBE_R21_2011_5_1;Integrated Security=SSPI"
.CommandType = 2
.CommandText = Array("Item")
.MaintainConnection = True
.CreatePivotTable TableDestination=xlBook.Worksheets("AP").Range("$a$1"), TableName="PivotTable3", DefaultVersion=xlPivotTableVersion12
End With

If someone could help me solve the above error that would be great!!!

Thanks in advance
Sathiya

Hello,

I am trying to create a pivot table macro so that I can produce a report of a senerio on one worksheet, from calculations on another worksheet. In other words, the basic function of the workbook is to create reports of various scenarios without using scenario manager.

I want to do this because the format of scenario manager is terrible and it makes it difficult organize my results.

So, I am trying to create a macro for the first time using pivot tables. The macro should run, then create a new column on my customized summary sheet where a pivot table is created so I can just get the values of the scenario, rather than the reference to the calculations on the other sheet. Once I have just the reported values I want the macro to put those values into specfied fields on my customized summary sheet.

I've tried creating this macro but keep running into this error:

"Run-time error '5': Invalid procedure call or argument"

Here is my code:


Sub German_Scenario()
'
' German_Scenario Macro
' Creates report for German Summary Sheet.
'
'
ActiveWindow.SmallScroll Down:=-21
ActiveCell.Offset(-7, 0).Range("A1").Select
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll Down:=-9
ActiveCell.Offset(4, 0).Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Inputs & Outputs!R9C4:R22C4", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Germany Summary!R35C4", TableName:= _
"PivotTable23", DefaultVersion:=xlPivotTableVersion12
Sheets("Germany Summary").Select
Cells(35, 4).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable23").PivotFields("value")
.Orientation = xlRowField
.Position = 1
End With
ActiveWindow.SmallScroll Down:=-15
ActiveCell.Offset(-31, 0).Range("A1").Select
ActiveWindow.SmallScroll Down:=24
ActiveCell.Offset(32, 0).Range("A1:A13").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-24
ActiveCell.Offset(-32, 0).Range("A1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=27
ActiveCell.Offset(47, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Inputs & Outputs!R10C7:R15C7", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Germany Summary!R51C4", TableName:= _
"PivotTable24", DefaultVersion:=xlPivotTableVersion12
Sheets("Germany Summary").Select
Cells(51, 4).Select
ActiveWindow.SmallScroll Down:=9
ActiveCell.Select
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Inputs & Outputs!R9C7:R15C7", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Germany Summary!R51C4", TableName:= _
"PivotTable25", DefaultVersion:=xlPivotTableVersion12
Sheets("Germany Summary").Select
Cells(51, 4).Select
ActiveSheet.PivotTables("PivotTable25").AddDataField ActiveSheet.PivotTables( _
"PivotTable25").PivotFields("value"), "Sum of value", xlSum
With ActiveSheet.PivotTables("PivotTable25").PivotFields("Sum of value")
.Orientation = xlRowField
.Position = 1
End With
ActiveCell.Offset(1, 0).Range("A1:A6").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-24
ActiveCell.Offset(-34, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(8, 3).Range("A1").Select
ActiveWindow.SmallScroll Down:=-12
End Sub



Thanks!

I truly need help with how to extract a large text file with over 4094 columns onto excel with VBA codes. I want it to organise with delimit and everything. Than I would like to use VBA code in excel to create a Pivot Table. Please I need Help.

I have one excel 2007 pivot table and need to create 180 pie charts and bar charts for individual customer reports. Any answers - preferably without code?

Hi,

I have succesffuly imported data from my ADO connection to my worksheet. However, i would like to know

1 - how do I create a pivot table with my recordset object

Thanks in advance

Hi....I dont know if I am the right path....

VB:

 
 'Initiate PivotCache object to accept external data
Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) 
 
 
 'Assign the recordset to PivotCache object
Set objPivotCache.Recordset = rs 
 
 'Create pivot table
With objPivotCache 
    .CreatePivotTable TableDestination:=ws.Range("Z10") 
     'Use this line of code instead if you want to specify a tablename for the pivot table
     '.CreatePivotTable TableDestination:=ws.Range("Z10"), TableName:="ABCDEFG"
End With 
 
 'Place the pivot field items in table
 'Place the field item "Week" into the column section
 
[COLOR=red]With objPivotCache.PivotTables <--- code Is faulty starting from this point 
[/COLOR] 
With .PivotFields 
     
    For i = 1 To .PivotFields.Count 
         
        If .PivotFields(i).Name = "Week" Then 
             
            .Orientation = xlColumnField 
             
        Else 
             
            .Orientation = xlDataField 
             
        End If 
         
    Next 
     
End With 
 
End With 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

I am using excel 2010. I am trying to create pivot table between two worksheets 'Summary' and 'Summary2'. I have identical row of data ranging from cells B5 to F5 in row 5 in both worksheets. Data in the two worksheets looks like this:
Summary worksheet: Issues,20,3,4,5
Summary2 worksheet: Issues,10,0,3,9
Worksheet referes to issues from location 1 and worksheet referes to issues from location 2. Col B has title 'issues', Col C refers to issues of customer 1, Col D refers to issues of customer 2, Col E refers to issues of customer 3, Col F refers to issues of customer 4
I go to a third worksheet and start pivot table and in the table range I give this: 'Summary:Summary2'!$B$5:$F$5. Then I Say OK. Gives error "data reference source is not valid". Can someone tell me how to select the row from two different worksheet in pivot table?
Also I want to be able to add issues of customers between two locations and get % completion for each locaiton.
Can someone please help?

Hi:

I have created several pivot tables on different sheets/tabs. I want to be able to summarize the totals in each field of these pivot tables "Year to date". So each sheet has a pivot table that sums the data for that month and then a second pivot table that references that months and the months before so that I can having a running YTD total.

I have tried selecting a range from the monthly pivot table data over more than one sheet to create the YTD pivot table, but keep getting a message that "data source reference is not valid". The same happens when I try to go to the original source data for the monthly PTs in each tab. It may be a small detail that I am missing, or perhaps you can't do this with pivot tables?

If anyone knows how to do this with PT's, help would be great. If there is a better way other than PT's that anyone knows of that would be great too. Thanks in advance.