VBA Pivot Table Macro used on multiple tabs

[Mark Bit]

Tried Searching through the forum but cannot seem to find anything on it - sorry if I missed it ...

Anyway, I am trying to create a VBA macro for fairly simple Pivot Table on a running report. Each week I get new data, similar data that has new values. I am looking to dump the data into each/new tab each week, run the macro, and have it display a new pivot table - within the (new) active worksheet. So there will be 52 weeks, 52 tabs, each with a Pivot Table. My problem lies with having to name the table within the VBA.

Is there a way to not set up a Pivot Name and have it default to whatever it wants within the new worksheet?

Or possibly a way to have it dynamically name itself using the active, given worksheet?


What I got so far:

Option Explicit
Public strPageName As String
Public strRowName As String
Public strColumnName As String
Public strDataName As String

Sub Format()
   
    Rows("1:5").Select
    Selection.Delete Shift:=xlUp
    Range("A1:J1").Select
    Selection.UnMerge
   
    Range("A1").Select
    ActiveCell = "Vendor #"
    Range("B1").Select
    ActiveCell = "Vendor Name"
    Range("D1").Select
    ActiveCell = "Parent Company"
    Range("F1").Select
    ActiveCell = "Retailer"
    Range("H1").Select
    ActiveCell = "Part Description"
    Range("I1").Select
    ActiveCell = "Part #"
    Range("M1").Select
    ActiveCell = "Week 1 Net"
End Sub

Public Sub MakePivot()
    Dim Destination As Range
    Dim DataRange As Range
   
    Set Destination = ActiveSheet.Range("V4")
    Set DataRange = Range("A1", Range("T1").End(xlDown))
   
    ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=DataRange) _
    .CreatePivotTable TableDestination:=Destination

End Sub

Sub SetPivot()

Dim pvtTable As PivotTable
Set pvtTable = ActiveWorksheet.PivotTables
With pvtTable
    .PivotFields(strPageName).Orientation = xlPageField
    .PivotFields(strRowName).Orientation = xlRowField
    .PivotFields(strColumnName).Orientation = xlColumnField
    .PivotFields(strDataName).Orientation = xlDataField
End With

End Sub

Thanks guys!

 

[Smallman]

Hi Mark

You have only provided part of your code. These:

strPageName
strRowName
strColumnName
strDataName

Are only mentioned at the start and end with no variables assigned in the middle.

This should format your cells

Sub Format()
    Rows("1:5").Delete Shift:=xlUp
    Range("A1:J1").UnMerge

    [A1] = "Vendor #": [B1] = "Vendor Name"
    [D1] = "Parent Company": [F1] = "Retailer"
    [H1] = "Part Description": [I1] = "Part #"
    [M1] = "Week 1 Net"
End Sub

While the following will make a pivot table on the active sheet.

Sub GetPiv()
    Dim PTCache As PivotCache
    Dim Pt As PivotTable
    Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, [A1].CurrentRegion)
    Set Pt = ActiveSheet.PivotTables.Add(PTCache, [V4])
 
        Pt.PivotFields("Desc").Orientation = xlColumnField
        Pt.PivotFields("Test1").Orientation = xlRowField
        Pt.PivotFields("Test2").Orientation = xlDataField
        Pt.PivotFields("Test3").Orientation = xlPageField
    
End Sub

Just replace Desc, Test1.2.3 with your variables.

I will attach a file to prove workings.

Take care

Smallman

 

[Mark Bit]

Hi Mark

You have only provided part of your code. These:

strPageName
strRowName
strColumnName
strDataName

Are only mentioned at the start and end with no variables assigned in the middle.

This should format your cells

Sub Format()
    Rows("1:5").Delete Shift:=xlUp
    Range("A1:J1").UnMerge

    [A1] = "Vendor #": [B1] = "Vendor Name"
    [D1] = "Parent Company": [F1] = "Retailer"
    [H1] = "Part Description": [I1] = "Part #"
    [M1] = "Week 1 Net"
End Sub

While the following will make a pivot table on the active sheet.

Sub GetPiv()
    Dim PTCache As PivotCache
    Dim Pt As PivotTable
    Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, [A1].CurrentRegion)
    Set Pt = ActiveSheet.PivotTables.Add(PTCache, [V4])

        Pt.PivotFields("Desc").Orientation = xlColumnField
        Pt.PivotFields("Test1").Orientation = xlRowField
        Pt.PivotFields("Test2").Orientation = xlDataField
        Pt.PivotFields("Test3").Orientation = xlPageField
   
End Sub

Just replace Desc, Test1.2.3 with your variables.

I will attach a file to prove workings.

Take care

Smallman

Thanks so much! This really helped so much. Sorry for the late "Thank You" - I have been off work and just got back to tackling this today. Now I just need to set up a VBA to initiate the full macro (both) when "activated" by pasting data in. That would be so ideal. That and having the Pivot automatically filter out data values that are 0 or blank.

But so you know, this is what I went with - seeing as I had some issues with the Count/Sum settings that everyone under the sun faces:

Sub Format()
     Rows("1:5").Delete Shift:=xlUp
     Range("A1:J1").UnMerge
   
     [A1] = "Vendor #": [B1] = "Vendor Name"
     [D1] = "Parent Company": [F1] = "Retailer"
     [H1] = "Part Description": [I1] = "Part #"
     [M1] = "Week 1 Net"
End Sub

Sub GetPiv()
    Dim PTCache As PivotCache
    Dim Pt As PivotTable
    Dim pf As PivotField
    Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, [A1].CurrentRegion)
    Set Pt = ActiveSheet.PivotTables.Add(PTCache, [V4])
    With Pt
        .PivotFields("Retailer").Orientation = xlRowField
        .PivotFields("Part #").Orientation = xlRowField
    End With
       
    With Pt.PivotFields("Week 1 Net")
        .Orientation = xlDataField
        .Function = xlSum
    End With
       
End Sub