VBA: Custom Sheetname that keeps on counting

Hi all,

I have a simple problem, but since I am nog an expert in VBA (yet), I hope someone here can provide me with the solution.

I created a VBA code that creates a Pivot table at another sheet. With 'newsheet' I run the macro over and over again and everytime it creates a different sheet. Now I would like to have the same but then Excel should automatically add a number to a custom sheetname. So far I was only able that Excel gives the name once. How can I add something like 'count worksheets + 1', so that the new sheet changes in this example to sheetname_Pivot1, sheetname_Pivot2, sheetname_Pivot3 etc.

Below you can find my code.

TIA, KR!
Paul

----

Dim dataname As String
Dim datasheetname As String
Dim pivotsheetname As String

    dataname = ActiveSheet.ListObjects(1).Name
    datasheetname = ActiveSheet.Name
    pivotsheetname = datasheetname & "_Pivot"
    
    Sheets.Add
    ActiveSheet.Name = pivotsheetname
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=dataname, Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:=pivotsheetname & "!R3C1", TableName:="PivotTable7", DefaultVersion:=xlPivotTableVersion15
    Sheets(pivotsheetname).Select
    Cells(3, 1).Select

-----