Same Pivot Tables Macro on Different Sheets

[Katterman]

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

Range("Table3[[#Headers],[Tech '#]]").Select

and Changed To

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.

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)

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

 

[Andrew Poulsom]

I suspect the problem is with:

SourceData:= "Table3"

A workbook cannot have more than one Table with the same name.

 

[Katterman]

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

Dim MySource As String

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

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

Thanks Again
Scott

 

[Domenic]

Try...

Dim MySource As Range

Set MySource = ActiveSheet.ListObjects(1).Range

SourceData:= MySource

Hope this helps!

 

[Katterman]

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

    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

 

[Katterman]

NO Need now, I got past it by adding

    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

 

[Domenic]

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

[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!

 

[Domenic]

Sorry, I misunderstood. Please ignore my last post....