Macro To Delete/Add & Rename/Arrange Sheets

[steven.holloway]

I have lost my way on trying to create this macro, can anyone help please.

Many thanks for any help

1) I have variable number of sheets between two sheets called ">" & "<", I
would like the first part of the macro to delete all sheets (if any) between
these two sheets.

2) I have a range of numbers (that can be repeated), I would like the macro
to run through the range and add a "TEMPLATE" sheet in between the two sheets
">" & "<" and rename the new sheet equal to the current number in the range,
but only if it does not already exist.

3) I would like the sheets in between the two sheets ">" & "<" to be shown
in accending order

PS. I already have about 60 sheets in the workbook and the above macro could
up to another 40+, is there a limit to the number of sheets a macro can add?

 

[Joel]

The macro below will delete the sheets as required. It requires a sheet
called Template (templet?) and copies the sheet and renames the new sheet.
It also expects the Range of number to be on a sheet called Numbers. change
the range on this sheet as required. the code uses advancefilter to get the
unique numbers in this range.

Sub Addtemplet()

'remove sheets
Application.DisplayAlerts = False
DeleteSheet = False
For Each sht In Sheets
If DeleteSheet = False Then
If sht.Name = ">" Then
DeleteSheet = True
End If
Else
If sht.Name = "<" Then
Exit For
End If
sht.Delete
End If
Next sht
Application.DisplayAlerts = False

With Sheets("Numbers")
Set UniqueNumbers = Range("A1:A25").AdvancedFilter _
(Action:=xlFilterInPlace, _
unique:=True)
For Each Num In UniqueNumbers
Set newsht = Worksheet("Template").Copy(before:=Sheets("<"))
newsht.Name = Num
Next Num
End Sub

 

[steven.holloway]

Thanks Joel, the first part of this works well, but I am having a few issues
with the second part.

I am getting a runtime error 13 - type mismatch and debug takes me to the
"Set UniqueNumbers" code. I have checked your code for this line against
recording the operation manually and it is the same, but does not seem to
work in a Macro scenario.

Instead of using this advanced filter, can we put code in to say if number
already exists as sheet skip number, else and sheet called number?

Many thanks again for your help

 

[Joel]

This should work.

Sub Addtemplet()

'remove sheets
Application.DisplayAlerts = False
DeleteSheet = False
For Each sht In Sheets
If DeleteSheet = False Then
If sht.Name = ">" Then
DeleteSheet = True
End If
Else
If sht.Name = "<" Then
Exit For
End If
sht.Delete
End If
Next sht
Application.DisplayAlerts = False

With Sheets("Numbers")
.Range("A1:A25").AdvancedFilter _
Action:=xlFilterInPlace, _
unique:=True
Set UniqueNumbers = .Range("A1:A25"). _
SpecialCells(xlCellTypeVisible)
For Each Num In UniqueNumbers
If Num <> "" Then
Worksheets("Template") _
.Copy before:=Sheets("<")
ActiveSheet.Name = Num
End If
Next Num
End With
End Sub

 

[steven.holloway]

Hi Joel, the problem has moved on.

The macro is adding new sheets as required, but stops before the end and
errors out on the .Copy before:=Sheets("<") line, despite the fact it has
already performed this several times. If I then re-run the macro for a second
time it will not add any new sheets.

Is there a limit to the number of sheets in a workbook and does Excel keep
track of previous sheets created even if deleted?

Many thanks for your help
Steve

 

[Joel]

See spreadsheet help Excel Specification and limits. the code probably
delete the Numbers worksheets. Make sure the Numbers sheet is not inbetween
the < and >.