How to add identical slicers to multiple sheets with identical Tables headers on each sheet

[Marnel_Runner]

Hi all

I'm new here, and have been unable to find a solution to my specific query online or in forums, so I hope somebody can help me. The problem is with Slicers. I work in construction, pricing up potential projects. We produce a 'Report' from our software and I have manipulated this data into discreet sheets, based on their area of the country, let's say South, London, North and Scotland. There are 4 sheets, each with identical Tables, but with data specific to each area. Each Table in named to match the Sheet name and area in the country, so, South, London, North and Scotland.

I have recorded the process and then tried to manipulate the code to use variables, specific to each Sheet and Table, along with the Field headers I want to filter by, but to no avail. Please help!

So... Specific data, held in identical Tables, named as the sheet name. Each sheet is named and I want to add slicers for two fields, 'Our Win %' and 'Stage', on every sheet of the workbook that has a table.

So far I have the following code, which gives me a Run-time error 5 - Invalid procedure call or argument, at the first ActiveWorkbook.SlicerCaches.Add2 statement:

Sub AddSlicers()
    
    Dim Source As String
    Dim ptName1 As String
    Dim ptName2 As String
        
    wsCount = Worksheets.Count
    
    For WSLoopCount = 3 To wsCount
        Worksheets(WSLoopCount).Select
        
        Source = "ActiveSheet.ListObjects(" & Chr(34) & ActiveSheet.Name & Chr(34) & ")"
        MsgBox Source
        
        ptName1 = Chr(34) & "Our Win % " & WSLoopCount & Chr(34)
        MsgBox ptName1
        
        ptName2 = Chr(34) & "Stage " & WSLoopCount & Chr(34)
        MsgBox ptName2
        
        ActiveSheet.Rows("1:6").Insert
        
        Range("A7").Select
[FONT=arial black]        ActiveWorkbook.SlicerCaches.Add2(Source _[/FONT]
[FONT=arial black]            , "Our Win %").Slicers.Add ActiveSheet, , ptName1, "Our Win %", 0, 0, 150, _[/FONT]
[FONT=arial black]            102[/FONT]
        ActiveWorkbook.SlicerCaches.Add2(Source _
            , "Stage").Slicers.Add ActiveSheet, , ptName2, "Stage", 0, 150, 150, 102
    Next
            
End Sub

If you are able to guide me to a worked example, or a clear description of the constituent terms and their usage, that would be great. If you are able to revise my code and explain where I have been going wrong, that would be fantastic!

I've been trying to solve this for over 11 hours now and can't find a way through.

Cheers

 

[Marnel_Runner]

p.s. I am starting at sheet 3 as the first two contain the original full data set and the second, my working out.