Trying to create a pivot table with a macro

[zookeepertx]

Hello all!

I'm trying to create 2 pivot tables on a spreadsheet; both tables will use the same data. This would probably be going better if I had a good handle on pivot tables, but I still had delusions of creating this macro without help. That plan has failed.
I've tried and tried to get my brain wrapped around the darn tables without success. Reading instructions does me no good; I need to be hands on and nobody around here knows anything about them either!

So, anyway, with that said - I tried recording a macro to create the 2 tables needed but, of course, it didn't work. I've been Googling the better part of the afternoon and keep altering and tweaking the code, just trying to get it to create ONE of the tables, thinking I'd move on from there. I've made such a mess of the code that it's not recognizable anymore, so I've trashed it.

I'm attaching a very shortened version of what's needed. Basically, I need to select A-R and down to the last row, sort by M, then make the tables. The first one needs to start in U2, having U showing the DIVISION and T showing the count of lines in each division. The second table needs to start in X2, having X showing the DIVISION divided by Vendor Name and Y showing the count of each vendor by division.

I'm sure I'm over-thinking it and you all will get it right off. I have all the faith in the world in you folks!

Thanks

Jenny

[raman1667]

Hi Jenny,

PFB for the required code and PFA for the sample data and macro file. I hope this code helps.

Option Explicit

Sub creating_pivot_tables()


Application.ScreenUpdating = False

Dim pivottable1 As PivotTable
Dim pivotField1 As PivotField
Dim last_row As Integer
Dim range1 As Range

'code for deleting pivot table in activesheet, if exist
For Each pivottable1 In ActiveSheet.PivotTables
        ActiveSheet.Range(pivottable1.TableRange2.Address).Delete
Next pivottable1
    
last_row = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
Set range1 = ActiveSheet.Range("A1:R" & last_row)
    
    

    
Range("A1").Select
Selection.AutoFilter

ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range _
    ("M1:M" & last_row), SortOn:=xlSortOnValues, Order:=xlAscending         'sorting data in column M
    
With ActiveSheet.AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
    
    Selection.AutoFilter
        
    'applying first pivot on the data
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        range1, Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="'[creating pivot tables.xlsm]Sheet3'!R2C21", TableName:= _
        "Pivot1", DefaultVersion:=xlPivotTableVersion15
    

    'Adding division column
    ActiveSheet.PivotTables("Pivot1").AddDataField ActiveSheet.PivotTables( _
        "Pivot1").PivotFields("DIVISION"), "Count of DIVISION", xlCount
        
    With ActiveSheet.PivotTables("Pivot1").PivotFields("DIVISION")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    'selecting and copying existing pivot table
    ActiveSheet.PivotTables("Pivot1").PivotSelect "", xlDataAndLabel, True
    Selection.Copy
    
    'creating second pivot table
    ActiveSheet.Range("X2").PasteSpecial
    
    Application.CutCopyMode = False
    
        
    'renaming second pivot table
    ActiveSheet.PivotTables(1).Name = "Pivot2"

    'adding vendor name column
    With ActiveSheet.PivotTables("Pivot2").PivotFields("Vendor Name")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    ActiveSheet.PivotTables("Pivot2").PivotFields("DIVISION").LayoutForm = _
        xlTabular
    
    ActiveSheet.PivotTables("Pivot2").PivotSelect "DIVISION[All;Total]", _
        xlDataAndLabel, True
        
    
    'removing subtotal from second pivot table
    With ActiveSheet.PivotTables("Pivot2")
        For Each pivotField1 In .PivotFields
            pivotField1.Subtotals(1) = False
        Next pivotField1
    End With
    
    Range("A1").Select
    
        
End Sub

Regards
Ramandeep Singh

[zookeepertx]

Hello!
Thank you very much for your reply! I will be back at work on Monday and will give it a try then. I'll be sure to come back and let you know how it goes.

Jenny

[zookeepertx]

Sorry for being so long in getting back to you, but it's Month End here at work and I'm in the Acctg Dept, so I've been swamped.

So, I tried your coding and it gives me part of what I need. I did have to replace DefaultVersion:=xlPivotTableVersion15 with Version 14 to get past an error message. (I'm on 2010)

Just a couple of problems, though. I'm re-attaching my workbook to show the trouble. As before, sheet 1 shows what I need; Sheet 3 shows what your coding gives me. In the second table, the vendor names need to be oriented under the divisions, but in the same column. (See sheet 1). And the "Count of..." column (which should be column "Y") needs to be a count of each vendor within each division. Also, at the bottom of each table is a row that says (blank); can we get rid of that?

I've been googling, tweaking, recorded and re-recorded macros trying to hit just the right wording in the code, but I'm going crazy! I'm sure you'll see what's wrong right away; definitely faster than I EVER would!

Thanks for your help!

Jenny

[zookeepertx]

Does anyone have any input on this problem? I spent about half a day on Friday trying again to get it to work correctly and got absolutely nowhere!