if sheet name is taken, add number at the end

[Twollaston]

Hey everyone,

I have a macro that creates a data table on one sheet and a pivot table on another sheet, and the problem I'm having is that i can only run the macro once and then I have to change the sheet names to do it again. I'm trying to make it so that if the sheet name is taken, then it will add a 1,2,3,4,5 etc... at the end, depending on how many times it's run.

If anyone can help me ammend my code that would be greatly appreciated.

I was hoping to do this for both sheet "Data" and "Summary"

Sub PivotTableCreation()


'Copies Query To New Sheet


ActiveSheet.Cells.Select
Application.CutCopyMode = False
    Selection.Copy
    Sheets.Add.Name = "Data"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, transpose:=False


' Creates Pivot Table With Dynamic Range


    Rows("1:4").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select


'Dynamic Range Table
ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name _
= "NewTable"


'Table Style
ActiveSheet.ListObjects("NewTable").TableStyle = "TableStyleMedium17"




'New Sheet For Pivot Table
Sheets.Add.Name = "Summary"


'Pivot Table Creation
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="NewTable", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
:="Summary!R3C1", TableName:="InsertNameHere", DefaultVersion:=xlPivotTableVersion14






End Sub

 

[Akuini]

Glad you figured it out.
You're welcome, glad to help, & thanks for the feedback.