Thread: Name Sheet for a pivot Table

Hy!
I need some help a with this macro.
This code is creating successfully a pivot table in a sheet called "SheetY" where Y is the next number available for sheets .
Can anyone tell me how can i pinpoint a fixed destination for the pivottable (eg : "new_sheet").
Running the macro several times will create Sheet4,5,6,7,8 etc.

Code:

Sub CreatePiovot()
Dim strHead As String
Dim strSheetName As String
Dim strListAddress As String

    
    strHead = Selection.Cells(1, 1)
    strSheetName = "'" & ActiveSheet.Name & "'!"


    strListAddress = Selection.Address(ReferenceStyle:=xlR1C1)
  
   ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=  strSheetName & _
strListAddress).CreatePivotTable TableDestination:="", TableName:="pivot"
 
     ActiveSheet.PivotTables("pivot").AddFields RowFields:=strHead

        With ActiveSheet.PivotTables("pivot").PivotFields(strHead)
    .Orientation = xlDataField
    .Caption = "Results"
    .Function = xlCount

        End With
        
        ActiveWorkbook.ShowPivotTableFieldList = False

        Application.CommandBars("PivotTable").Visible = False

    

End Sub

Have a nice day !

I have found a solution, and that is to add before end sub()

Code:

  ActiveSheet.Name = "new_sheet"

Please take a few minutes to read the forum rules (link in menu bar), and then edit both of your posts to add code tags.