worksheets.add question

[lezawang]

Hi
I want to add a sheet but I want to keep sheet1 as an active one. I named sheet1 as "Hello". Now when I run the code below, it will do these 2 things

1) Adding a sheet before sheet1! why is that? In excel when I click + to add a sheet, it will add it after the active one not before like what vba is doing

2) the code will activate the new sheet that is created but not my first sheet which is called "Hello".

How can I fix that, I want to add a sheet but keep sheet1 activated. Thank you so much

Sub worksheet_add()
    Workbooks(1).Worksheets.Add
    Workbooks(1).Worksheets(1).Activate
End Sub

 

[Fluff]

Try

Sub lezawang()
   Sheets.Add , Sheets(1)
   Sheets(1).Activate
End Sub

 

[alansidman]

Here is some code I use to add sheets. You can modify as needed.

Option Explicit




Sub AddSheets()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim sname As String
    With Sheets("Sheet1")
        For i = 1 To lr
            sname = .Range("A" & i)
            Sheets.Add After:=Sheets(Sheets.Count)
            Sheets(Sheets.Count).Name = sname
        Next i
    End With
End Sub

 

[lezawang]

Thank you all. I got it

 

[Fluff]

Glad we could help & thanks for the feedback