Duplicate Sheet and Rename based on Sheet Count

[urchino]

Hello people,

I am trying to duplicate sheet every time a user click on a button and it will be rename into a sequence.

For example i have sheets ABC, DEF, GHI and the master to duplicate from is 99.
Hence the sheet count is 4. When it is being copied from 99, i want the duplicated sheet to rename 01, 02 and so on. Or 1, 2, and so on.

I found some online but i could not customise to what i need.

Any idea?

 Sub A_001()

'Duplicate Sheet from master


Dim shtname As String


Set WS = Sheets("99")
 
WS.Copy After:=Sheets(Sheets.Count)


shtname = Sheets(Sheets.Count) - 6


ActiveSheet.Name = shtname


Worksheets("99").Move After:=Sheets(Sheets.Count)


End Sub

 

[Trebor76]

Hi urchin,

Though (in my humble opinion) separate tabs with identical layout is usually not the way to go, try this:

Option Explicit
Sub Macro2()
    
    Dim lngValSheetNameArr() As Long
    Dim lngArrayIndex As Long
    Dim wsMySheet As Worksheet
    
    Application.ScreenUpdating = False
    
    'Create an array of numeric sheet names, excluding a tab named '99'
    For Each wsMySheet In ThisWorkbook.Sheets
        If IsNumeric(wsMySheet.Name) = True And Val(wsMySheet.Name) <> 99 Then
            lngArrayIndex = lngArrayIndex + 1
            ReDim Preserve lngValSheetNameArr(lngArrayIndex)
            lngValSheetNameArr(lngArrayIndex) = CLng(wsMySheet.Name)
        End If
    Next wsMySheet
    
    'Copy template tab '99' to the end of the workbook
    Worksheets("99").Copy After:=Sheets(Sheets.Count)
    'If there's no tab with a value (excluding '99'), then...
    If lngArrayIndex = 0 Then
        '...name the last tab 1
        Sheets(Sheets.Count).Name = 1
    'Else...
    Else
        '...name the last tab as the highest number in the 'lngValSheetNameArr'
        'array and increment that number by one
        Sheets(Sheets.Count).Name = Val(WorksheetFunction.Max(lngValSheetNameArr)) + 1
    End If
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[urchino]

Hi Trebor,

Thank you for the codes. It work wonders. Just to understand more, why do you think it is not recommended to have identical layout?

The reason i'm using is for the sales guys to input the breakdown by products and the separate tabs is to split by customers. Any recommendation on how to better manage this?

Thanks a lot.

Regards
Urchino

 

[Trebor76]

I try and following a database methodology where there's one tab for the data (like a table in a database) where reports are then derived from in another tab (similar to queries and reports in a database). This tab could have a drop-down of all your customers which has lookup formulas to populate relevant cells back to tab containing the data whenever a customer is selected.

This means there are far fewer tabs which has an obvious maintenance benefit.

Sounds like you're happy with my solution so that's the main thing.

Thanks for the feedback and the thanks

Robert