CODEWITHSUNDEEP
excelvba
PJCAN1
PJCAN1

Reputation: 11

Excel Template Copy and dynamic naming

I have a worksheet which acts as a master. I want to be able to run a macro that will create a copy of the worksheet and rename it, it should be called CQ X where X represents the next number - so CQ5, CQ6 etc. I can obviously copy the worksheet and rename it quite easily but i don't know how best to work out the next number and then add that in?

Upvotes: 0

Views: 33

Answers (1)

Davesexcel
Davesexcel

Reputation: 6984

You can count how many sheet.names have "CQ" and then copy and name the copied sheet tp the next numbers

Sub CountAndCopy()
    Dim WS As Worksheet, sh As Worksheet, iCnt As Long, x

    Set WS = Worksheets("Template")

    For Each sh In Sheets
        If InStr(1, sh.Name, "CQ", vbTextCompare) = 1 Then iCnt = iCnt + 1
    Next sh

    x = CStr(iCnt)

    WS.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "CQ " & x + 1
    WS.Select

End Sub

Upvotes: 1

Related Questions