Heelertreats
New Member
- Joined
- Jan 8, 2021
- Messages
- 6
- Platform
- 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":
Any insights are much appreciated.
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.