Sheets.Add trouble

[Heelertreats]

Hi all.

I've got a workbook with three worksheets:
Master
TEMPLATE
Lookups

I've got a button that creates/adds new worksheets based on a range in "Master" and pastes content from "TEMPLATE" to the new worksheets.

The problem is that I want the new worksheets to be inserted before "Lookups." I've tried several variations of Sheets.Add before/after, and everything I've tried alwasy overwrites my "Lookups" sheet and breaks.

Here is the existing VBA, which puts the new sheets after "Lookups", and that I'd like to revise to BEFORE "Lookups":

Sub CreateAndNameWorksheets()
    Dim c As Range
    Application.ScreenUpdating = False
    Sheets("Master").Select
    For Each c In Worksheets("Master").Range("A2:A1000")
        If (c.Value = "") Then
          GoTo Continue
        End If
        c.Select
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = c.Value
        Sheets("Template").Cells.Copy
        ActiveSheet.Paste
        Range("B5").Select
        Application.CutCopyMode = False
        Sheets("Master").Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & c.Value & "'" & "!A1", TextToDisplay:=c.Value
Continue:
    Next c
    Application.ScreenUpdating = True
    Worksheets("Master").Activate
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
End Sub

Any insights are much appreciated.

 

[RoryA]

Sheets.Add would never overwrite a worksheet. You'd use:

Sheets.Add Before:=Sheets("Lookups")

 

[Heelertreats]

Sheets.Add would never overwrite a worksheet. You'd use:

Sheets.Add Before:=Sheets("Lookups")

And that's why I'm so confused. Here's two pics, the first is the result of Sheets.Add After:=Sheets(Sheets.Count). The second is what happens with Sheets.Add Before:=Sheets("Lookups").

 

[Fluff]

Change this

Sheets(Sheets.Count).Name = c.Value

to

ActiveSheet.Name = c.Value

It's not overwriting the sheet it's simply renaming the wrong sheet as that line is renaming the last sheet in the workbook, but the sheet added is not the last sheet.

 

[Heelertreats]

Change this

Sheets(Sheets.Count).Name = c.Value

to

ActiveSheet.Name = c.Value

It's not overwriting the sheet it's simply renaming the wrong sheet as that line is renaming the last sheet in the workbook, but the sheet added is not the last sheet.

That was it! Thanks so much folks!

 

[Fluff]

Glad we could help & thanks for the feedback.