Sheets.Add trouble

Heelertreats

New Member
Joined
Jan 8, 2021
Messages
6
Platform
  1. Windows
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":

VBA Code:
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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,534
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Sheets.Add would never overwrite a worksheet. You'd use:

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

Heelertreats

New Member
Joined
Jan 8, 2021
Messages
6
Platform
  1. Windows
Sheets.Add would never overwrite a worksheet. You'd use:

Code:
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").
 

Attachments

  • before.PNG
    before.PNG
    3.3 KB · Views: 3
  • after.PNG
    after.PNG
    2.5 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Change this
VBA Code:
Sheets(Sheets.Count).Name = c.Value
to
VBA Code:
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.
 
Solution

Heelertreats

New Member
Joined
Jan 8, 2021
Messages
6
Platform
  1. Windows
Change this
VBA Code:
Sheets(Sheets.Count).Name = c.Value
to
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,128
Messages
5,628,861
Members
416,345
Latest member
sayad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top