Same Pivot Tables Macro on Different Sheets

Katterman

Board Regular
Joined
May 15, 2014
Messages
103
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hello Everyone

Relative newbie here but learning quickly from all of your vast knowledge.

I'm having some pivot table issues when a Macro used to create the Pivots (from a table data on a sheet) when used on different sheets.
The macro works fine on my original Data sheet but when the sheet is duplicated the pivot table creation macro fails.
I fairly sure it's because of Pivot Table names that can't be duplicated but i'm unsure how to get around it.

Some info on the end goal for this "workbook".
This workbook (Created Monthly) will have an identical worksheet generated for every day (Each Sheet is Named for that Month/Day EG: Sept 1, Sept 2 etc). The each sheet contains a "Table" that Data is imported into from 2 other sheets (Basically just Temp sheets to clean up the data pasted in from other sources) and once the import is done I run a Macro to create 3 Pivot Tables on the same sheet. This will be done every day on the corresponding date / sheet. The data imported onto the "Sheet of the day" will always be different since it's for reporting on the days before's activity. On the first sheet which is essentially the "template" that is used to create the sheets for the month (and where the macro was "recorded and Tweaked from for the pivot tables), everything works perfectly when the macro is run. On any subsequent sheets, The Pivot table creation fails when the macro runs..

One part i did get past was the fact the actual Table Name the Pivot table is created changes on each sheet

I Had
Code:
Range("Table3[[#Headers],[Tech '#]]").Select
and Changed To
Code:
ActiveSheet.ListObjects(1).Range.Select

So far that went well but it fails further into the code now on every sheet except the original.
Like i said before, i'm sure it's because the Pivot table names (TableName:xxxx) need to not duplicate across the sheets
but unsure how to address.

Error is: Run-Time error '1004'
Select Method of Range class failed
and it Highlights" on this line when debugged.

Code:
ActiveSheet.PivotTables("VANPivot").PivotFields("Branch").CurrentPage = "VAN"

Here is my one of the 3 sections of code (The other 2 sections are for the other 2 Pivot tables created from the same table)

Code:
Sub Pivot_Tables_TEST()


Dim ShName As String
ShName = ActiveSheet.Name


    With Application
       .ScreenUpdating = False
    End With
   
    ActiveSheet.ListObjects(1).Range.Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table3", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _
        :=Worksheets(ShName).Range("I5"), TableName:="VANPivot", DefaultVersion:= _
        xlPivotTableVersion12
    ActiveSheet.Select
    Cells(5, 9).Select
    With ActiveSheet.PivotTables("VANPivot").PivotFields("Branch")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("VANPivot").PivotFields("Area")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("VANPivot").PivotFields("Tech Name")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("VANPivot").AddDataField ActiveSheet.PivotTables( _
        "VANPivot").PivotFields("WO"), "Count of WO", xlCount
    ActiveSheet.PivotTables("VANPivot").PivotFields("Branch").ClearAllFilters
    ActiveSheet.PivotTables("VANPivot").PivotFields("Branch").CurrentPage = _
        "VAN"
    ActiveWorkbook.ShowPivotTableFieldList = False


   
    With Application
       .ScreenUpdating = True
    End With
  
End Sub

Hopefully i'm going about this in the right direction but not opposed to being rerouted :)

Thanks In Advance for any and all replies.

Scott
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I suspect the problem is with:

Code:
SourceData:= "Table3"

A workbook cannot have more than one Table with the same name.
 
Upvote 0
Thanks for the Reply Andrew.

I suspected that was part of the problem, question is,
How do i grab the always changing Table name for each sheet and dynamically update the SourceData to equal "Table33", or "Table 334" etc.

I'm looking to have some code in the soucedata spot to replace the "Tablexx" with whatever the table name is on the sheet when the macro is run.

I'm still a relative newbie but tried adding the following in the appropriate areas of the code with No Luck

Code:
Dim MySource As String

MySource = ActiveSheet.ListObjects(1).Range.Select

[COLOR=#333333]and the      [/COLOR]SourceData:= MySource



Thanks Again
Scott
 
Upvote 0
Try...

Code:
Dim MySource As Range

Set MySource = ActiveSheet.ListObjects(1).Range

SourceData:= MySource

Hope this helps!
 
Upvote 0
Thanks Domenic

That Worked Perfectly :)

Now, New Issue Pops Up now that the code runs further.
Since the DAta going into the pivot tables is always different each day, i have a section of code that filters out some not needed "areas".
Sometimes that area is not contained in the original data and sometimes it is.

Problem is that when that data does not exist, an error comes back. Here is a snip-it of the code

Code:
    With ActiveSheet.PivotTables("KPivot").PivotFields("Area")
        .PivotItems("Van").Visible = False
        .PivotItems("Vic").Visible = False
        .PivotItems("Pender").Visible = False
        .PivotItems("(Blank)").Visible = False
If any of those does exist on a specific day i get an error back.

Any way to say something along the lines of if the item does not exist to just carry on?

Thanks so much again for yours and any additional help.

Thanks
Scott
 
Upvote 0
NO Need now, I got past it by adding

Code:
    With ActiveSheet.PivotTables("KPivot").PivotFields("Area")
     On Error Resume Next
        .PivotItems("Van").Visible = False
        .PivotItems("Vic").Visible = False
        .PivotItems("Pender").Visible = False
     On Error GoTo 0

Thanks Again To Andrew and Domenic for your help.

Scott
 
Upvote 0
Or, try setting the MissingItemsLimit property to 0 or xlMissingItemsNone, and refreshing the pivottable cache prior to setting the Visible property for your pivotitems...

Code:
[COLOR=darkblue]With[/COLOR] ActiveSheet.PivotTables("KPivot").PivotCache
    .MissingItemsLimit = 0 [COLOR=green]'xlMissingItemsNone[/COLOR]
    .Refresh
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

Hope this helps!
 
Upvote 0
Sorry, I misunderstood. Please ignore my last post....
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top