Macro to always add second sheet tab ?

[zone709]

Hi so what I am trying to do below is just add another tab, but I need to always add a tab and it to be second from first.

I have 15 tabs so I always want to add a new one as second tab and name it Errors. I tried worksheets (2) but debug.

[CODE]

Sub Macro3()

'
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Errors"
End Sub

[/CODE]

 

[Fluff]

How about

Sub Macro3()
    Sheets.Add(, Sheets(1)).name = "Errors"
End Sub

This assumes you will never have an existing sheet called Errors.

 

[JPARKHURST]

1. Are you replacing another worksheet named "errors"? (and how are you not if you are always adding another? Is this a template?)
2. Is your first worksheet named? Would you like to reference that vs. Activesheet?

 

[zone709]

Hi no i dont have an errors tab on any other sheet. What Fluff gave me works fine. I was needing something like this just in case I have lets say last tab selected. It will always send it to second thanks guys

 

[zone709]

Hi I actually ran into that issue. Since sometimes I will forget to delete the error tab. Can we tweak this. If the Error tab it there then skip this below?

[LEFT][COLOR=#333333][FONT=monospace]Sub Macro3()
    Sheets.Add(, Sheets(1)).name = "Errors"
End Sub[/FONT][/COLOR][/LEFT]

 

[Fluff]

Try

Sub Macro3()
   If Not Evaluate("isref(Errors!A1)") Then Sheets.Add(, Sheets(1)).name = "Errors"
End Sub

 

[JPARKHURST]

Fluff rocks. Mine is written in crayon, but I'm sure can be fixed up. Not happy about the multiple if statements, but my do while was not satisfactory for me.

 

[JPARKHURST]

Oops. Just for the fun of it.

Option Explicit
Private Sub CreateSheet(sNewSheetName As String)
    Dim wbActive As Workbook
    Dim wsFirst As Worksheet, wsNew As Worksheet
    
    Set wbActive = ThisWorkbook
    Set wsFirst = Worksheets(1)
    
    If Not VerifySheet(sNewSheetName) Then
        Set wsNew = wbActive.Sheets.Add(After:=wsFirst)
        wsNew.Name = sNewSheetName
        Else
            sNewSheetName = InputBox("Please enter a different sheet name to add")
            If Not VerifySheet(sNewSheetName) Then
                Set wsNew = wbActive.Sheets.Add(After:=wsFirst)
                wsNew.Name = sNewSheetName
                Else
                    sNewSheetName = MsgBox("Please erase some of the errant sheets")
                End If
    End If
    wsFirst.Activate
End Sub




 Private Function VerifySheet(sSName As String, Optional wbCurrent As Workbook) As Boolean
    Dim sSetSheet As Worksheet


     If wbCurrent Is Nothing Then Set wbCurrent = ActiveWorkbook
     On Error Resume Next
     Set sSetSheet = wbCurrent.Sheets(sSName)
     On Error GoTo 0
     VerifySheet = Not sSetSheet Is Nothing
 End Function




Private Sub CommandButton1_Click()
    Call CreateSheet("Errors")
End Sub

 

[zone709]

Oops. Just for the fun of it.

Option Explicit
Private Sub CreateSheet(sNewSheetName As String)
    Dim wbActive As Workbook
    Dim wsFirst As Worksheet, wsNew As Worksheet
    
    Set wbActive = ThisWorkbook
    Set wsFirst = Worksheets(1)
    
    If Not VerifySheet(sNewSheetName) Then
        Set wsNew = wbActive.Sheets.Add(After:=wsFirst)
        wsNew.Name = sNewSheetName
        Else
            sNewSheetName = InputBox("Please enter a different sheet name to add")
            If Not VerifySheet(sNewSheetName) Then
                Set wsNew = wbActive.Sheets.Add(After:=wsFirst)
                wsNew.Name = sNewSheetName
                Else
                    sNewSheetName = MsgBox("Please erase some of the errant sheets")
                End If
    End If
    wsFirst.Activate
End Sub




 Private Function VerifySheet(sSName As String, Optional wbCurrent As Workbook) As Boolean
    Dim sSetSheet As Worksheet


     If wbCurrent Is Nothing Then Set wbCurrent = ActiveWorkbook
     On Error Resume Next
     Set sSetSheet = wbCurrent.Sheets(sSName)
     On Error GoTo 0
     VerifySheet = Not sSetSheet Is Nothing
 End Function




Private Sub CommandButton1_Click()
    Call CreateSheet("Errors")
End Sub

Thanks guys again for the help