Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: if sheet name is taken, add number at the end

  1. #1
    Board Regular
    Join Date
    May 2019
    Posts
    216
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default if sheet name is taken, add number at the end

    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"

    Code:
    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
    Last edited by Twollaston; Aug 29th, 2019 at 10:32 AM.

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: if sheet name is taken, add number at the end

    Try this:

    Code:
    Sub PivotTableCreation()
    
    
    'Copies Query To New Sheet
    
    
    ActiveSheet.Cells.Select
    Application.CutCopyMode = False
        Selection.Copy
    ''    Sheets.Add.Name = "Data"
        Call addSheet("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"
    Call addSheet("Summary")
    
    'Pivot Table Creation
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="NewTable", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
    :="Summary!R3C1", TableName:="InsertNameHere", DefaultVersion:=xlPivotTableVersion14
    
    
    End Sub
    
    
    Private Sub addSheet(sName As String)
    Dim ws As Worksheet, z As Long, x
    Dim nm As String, q As String
    If Evaluate("ISREF('" & sName & "'!A1)") Then
    
         For Each ws In ActiveWorkbook.Worksheets
            nm = UCase(ws.Name): q = UCase(sName)
                If nm Like q & "*" And Len(nm) > Len(q) Then
                    x = Replace(nm, q, "")
                    If IsNumeric(x) Then
                        If z < x Then z = x
                    End If
                End If
        Next
        Sheets.Add.Name = sName & z + 1
    Else
    Sheets.Add.Name = sName
    End If
    
    End Sub

  3. #3
    Board Regular
    Join Date
    May 2019
    Posts
    216
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if sheet name is taken, add number at the end

    I got a "Runtime error '1004': Applicatoin-defined or object-defined error

    I tried to update the name to sName, but it's not working for me. Tried some different iterations, but i couldn't figure it out. Thanks for sharing that though, it works for the data sheet, just not the summary

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: if sheet name is taken, add number at the end

    it works for the data sheet, just not the summary
    Not sure why it works for the data sheet, but not the summary.
    When you run the code what is the active sheet?

    Which line is highlighted in yellow when the code stops?

    Edit:
    And for the moment, let's try something simple first, run this code and tell me what happen.

    Code:
    Sub try9()
    Call addSheet("Data")
    Call addSheet("Summary")
    
    End Sub
    Last edited by Akuini; Aug 29th, 2019 at 12:31 PM.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if sheet name is taken, add number at the end

    I've very quickly knocked this up for adding a sheet called "data" with an incrementing number. You should be able to replicate and edit this to add additional "summary" sheets as well.

    Code:
    Sub add_incrementing_worksheet_name()
    'count number of worksheets already in workbook with required name (data or data_)
        data_sheet_counter = 0
        For Each ws In ActiveWorkbook.Worksheets
            If UCase(ws.Name) = "DATA" Or UCase(Left(ws.Name, 5)) = "DATA_" Then
                data_sheet_counter = data_sheet_counter + 1
            End If
        Next ws
    'generate name to use
        If data_sheet_counter > 0 Then
            my_new_sheet_name = "data_" & data_sheet_counter + 1
        End If
    'add new worksheet at end of workbook and name it
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = my_new_sheet_name
    End Sub
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,575
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: if sheet name is taken, add number at the end

    You might use this UDF. Given a seed name, it will return the next indexed name with that root.

    If there is no sheet named "MySheet", NextSheetName("MySheet") will return "MySheet"
    If there is a sheet named "MySheet", it will return "MySheet(2)", unless that exists in which case it will return "MySheet(3)", etc.

    Note that the seedName can be in a sequence, i.e. NextSheetName("MySheet(2)") will return "MySheet(3)" if "MySheet(2)" already exits.

    Code like
    Code:
    Worksheets.Add.Name = NextSheetName("MySheet")
    will add a sheet in the "mySheet" series of worksheets.

    Code:
    Function NextSheetName(ByVal seedName As String, Optional wb As Workbook) As String
        Dim strRoot As String, Index As Long
        If wb Is Nothing Then Set wb = ThisWorkbook
        
        If SheetExists(seedName, wb) Then
            If seedName Like "*?(*)" Then
                strRoot = Split(seedName, "(")(0)
                Index = Val(Split(seedName, "(")(1))
            Else
                strRoot = seedName
                Index = 1
            End If
            NextSheetName = NextSheetName(strRoot & "(" & (Index + 1) & ")", wb)
        Else
            NextSheetName = seedName
        End If
    End Function
    
    Function SheetExists(sheetName As String, Optional wb As Workbook) As Boolean
        If wb Is Nothing Then Set wb = ThisWorkbook
        On Error Resume Next
        SheetExists = LCase(wb.Sheets(sheetName).Name) = LCase(sheetName)
        On Error GoTo 0
    End Function

  7. #7
    Board Regular
    Join Date
    May 2019
    Posts
    216
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if sheet name is taken, add number at the end

    Quote Originally Posted by JugglerJAF View Post
    I've very quickly knocked this up for adding a sheet called "data" with an incrementing number. You should be able to replicate and edit this to add additional "summary" sheets as well.

    Code:
    Sub add_incrementing_worksheet_name()
    'count number of worksheets already in workbook with required name (data or data_)
        data_sheet_counter = 0
        For Each ws In ActiveWorkbook.Worksheets
            If UCase(ws.Name) = "DATA" Or UCase(Left(ws.Name, 5)) = "DATA_" Then
                data_sheet_counter = data_sheet_counter + 1
            End If
        Next ws
    'generate name to use
        If data_sheet_counter > 0 Then
            my_new_sheet_name = "data_" & data_sheet_counter + 1
        End If
    'add new worksheet at end of workbook and name it
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = my_new_sheet_name
    End Sub
    Having the same problem as with the previous one, i get an error that references:
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = my_new_sheet_name
    Any ideas?

  8. #8
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: if sheet name is taken, add number at the end

    Can you answer my question in post #4 ?

  9. #9
    Board Regular
    Join Date
    May 2019
    Posts
    216
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if sheet name is taken, add number at the end

    Quote Originally Posted by Akuini View Post
    Not sure why it works for the data sheet, but not the summary.
    When you run the code what is the active sheet?

    Which line is highlighted in yellow when the code stops?

    Edit:
    And for the moment, let's try something simple first, run this code and tell me what happen.

    Code:
    Sub try9()
    Call addSheet("Data")
    Call addSheet("Summary")
    
    End Sub
    Yes that is working fine, it's creating both sheets for me with the correct number at the end, even if I keep doing it over and over

    The line I get the error on is:
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="NewTable", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:="Summary!R3C1", TableName:="InsertNameHere", DefaultVersion:=xlPivotTableVersion14

    Because that page already has a pivot table in that spot, so i was trying to think of a way to get the dynamic sheet name
    i tried grabbing the sName variable from the other sub AddSheet(sName As String)
    I'm not sure how to do that though, i tried making it a public sub, I tried a bunch of stuff, but I think I'm just making a simple mistake
    Last edited by Twollaston; Aug 29th, 2019 at 03:51 PM.

  10. #10
    Board Regular
    Join Date
    May 2019
    Posts
    216
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if sheet name is taken, add number at the end

    Okay Everyone, I was able to get it to work. Sorry for my extreme newbyness, the little things always mess me up!

    Thanks everyone who replied and helped me on here, I couldn't have done it without you guys!

    For anyone having trouble with something similar you can use the below code:


    Code:
    Sub PivotTableCreation()
    
    
    
    
    'Copies Query To New Sheet
    
    
    
    
    ActiveSheet.Cells.Select
    Application.CutCopyMode = False
        Selection.Copy
    ''    Sheets.Add.Name = "Data"
        Call addSheet("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"
    Call addSheet("Summary")
    SheetName = ActiveSheet.Name
    
    
    
    
    'Pivot Table Creation
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="NewTable", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:= _
    "'" & SheetName & "'!R3C1", TableName:="InsertNameHere", DefaultVersion:=xlPivotTableVersion14
    
    
    
    
    End Sub
    
    
    
    
    Private Sub addSheet(sName As String)
    Dim ws As Worksheet, z As Long, x
    Dim nm As String, q As String
    If Evaluate("ISREF('" & sName & "'!A1)") Then
    
    
         For Each ws In ActiveWorkbook.Worksheets
            nm = UCase(ws.Name): q = UCase(sName)
                If nm Like q & "*" And Len(nm) > Len(q) Then
                    x = Replace(nm, q, "")
                    If IsNumeric(x) Then
                        If z < x Then z = x
                    End If
                End If
        Next
        Sheets.Add.Name = sName & z + 1
    Else
    Sheets.Add.Name = sName
    End If
    
    
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •