How to calculate with sheet names as integers?

Question

I have many sheets(about 1000) with names as integer number like: 1 2 3 5 7 10 etc. They are ascending but not consistent, like I wrote. I have vba code that create new sheet with numbername from inputbox, after the activesheet(I activate sheet 3, run the code, enter 4 in inputbox and it creates sheet 4 after 3). What I need is solution how to (example): create a new sheet with name 4 after the sheetnumber 3, without haveing to be on sheet 3.

How to calculate with sheet names as integers? -> Use CInt() | Is this somewaht similar to http://stackoverflow.com/questions/6202074/vba-convert-string-to-int-if-string-is-a-number ? — Kai, Feb 18 '15 at 12:07
No, that would be for cell content. I can't come up with mathematical solution. I might be able to code it, but I don't understand the logic. — Jelovac, Feb 18 '15 at 12:10
As a workaround, you could use ActiveWorkbook.Sheets(3).Activate or ActiveWorkbook.Sheets("Sheet3").Activate to switch in your code to the right sheet, make a new one and avtivate the sheet u where in before. I don't get how you actual question is the same as your headline. Hope this helps. — Kai, Feb 18 '15 at 12:22
Well what you have just suggested is what I actually do. Problem is I have over 1000 sheets and it is not practical to search for next sheet lower than you need, to open needed sheet after it. I wan't vba to do that to preserve my working time. — Jelovac, Feb 18 '15 at 12:35

Paul Kelly · Accepted Answer · 2015-02-18T14:14:48.270

1

The following code with go through all the sheets. When it reaches one with a larger number it will insert the new sheet before it.

Public Sub Test()
    AddSheetWithNumber shNum:=4
End Sub

Public Sub AddSheetWithNumber(shNum As Long)

    With ThisWorkbook

        Dim sh As Worksheet
        For Each sh In Worksheets
            ' Find first sheet with number greater than new sheet number
            If CLng(sh.Name) > shNum Then
                ' Add worksheet before sheet with larger number
                .Worksheets.Add before:=sh
                ActiveSheet.Name = CStr(shNum)
                Exit For
            End If
        Next

    End With

End Sub

edited Feb 18 '15 at 14:14

answered Feb 18 '15 at 13:04

Paul Kelly

975
7
13

Yes that is exactly what I ment and want. Can you just subscribe what are CLng and CStr. ? TY – Jelovac Feb 18 '15 at 14:59
CLng converts a string to an integer -the sheet name is a string so you need to convert it to a number to compare. CStr converts a number to a string - you need to convert the new sheet number to a string so you can assign it as the sheet name. – Paul Kelly Feb 18 '15 at 17:00
@Jelovac That this was the correct answer wasn't clear to me from your question, it might be worth editing your question accordingly. – Aiken Feb 19 '15 at 08:56

score 0 · Answer 2 · answered Feb 19 '15 at 13:28

I came up with similar solution before I saw your example Paul. It might be usefull as well:

Sub New_Sheet()

Dim ExistingSheet As Integer
Dim NewSheet As Integer
On Error GoTo 30
NewSheet = InputBox("Enter new sheet:", "NEW SHEET")


For i = 3 To Sheets.Count - 3 'this is my work range
ExistingSheet = Sheets(i).Name
If ExistingSheet = NewSheet Then
 MsgBox ("That sheet already exist!")
 Sheets(i).Activate
 GoTo 30
Else
    On Error GoTo 10     'last 4 sheets have textual name like (subtotal, partners, etc) so error came up if I want to add sheet with bigest number(because it's comparing a textual name)
    If NewSheet > ExistingSheet Then   'error came up only in that case for now, so I make it place biggest sheet before textual one.
     GoTo 20
    Else
        ActiveWorkbook.Sheets("Empty Card").Copy _
        after:=Sheets(i - 1)                        'Sheet with formulas and tables prepared for work
        ActiveSheet.Name = NewSheet
        ActiveSheet.Cells(2, 13) = ActiveSheet.Name
    Exit Sub
    End If
End If
20 Next i
10      ActiveWorkbook.Sheets("Empty card").Copy _
        after:=Sheets(i - 1)      'didn't know of "befor" command
        ActiveSheet.Name = NewSheet
        ActiveSheet.Cells(2, 13) = ActiveSheet.Name
30 Exit Sub

End Sub

How to calculate with sheet names as integers?

2 Answers2