Apply different charts across multiple Excel sheets (according to line numbers on each sheet)

I created a PivotChart showing major cities as "filter" (80 in total) and some performance metrics as "sum of values". In the Excel file, I have 26 sheets corresponding to the countries of these cities, and on each sheet a table with a list of cities (for some countries it may be 1 line (1 city), 5 lines or something else ..)

In each country sheet, I have to show this table (already done) along with a graph for each row of the table's performance (again, it could be 1, 5, or whatever).

I thought the easiest way to accomplish this is to insert a pivot chart in each sheet and then automatically select each row with the city name and apply it to the hinge filter.

So far, this is the code I wrote:

Sub PastPivot() 
    Dim sht As Worksheet
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.ChartArea.Copy
    For Each sht In Worksheets
        sht.Range("a16").PasteSpecial
    Next
    Application.CutCopyMode = False
End Sub

      

        
        
        
      

    

but I have a few questions:

• It runs "error 5" when running the code

• I'm not sure how to write the code to execute as many charts as there are rows on each sheet (and how to determine the position of the chart, given that I won't have the same number of charts on each sheet).

Perhaps the solution is to use a different type of graph rather than pivot.

Any advice on how to accomplish it?