Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro To Delete/Add & Rename/Arrange Sheets
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro To Delete/Add & Rename/Arrange Sheets
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(befo=Sheets("<")) newsht.Name = Num Next Num End Sub "steven.holloway" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro To Delete/Add & Rename/Arrange Sheets
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" wrote: 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(befo=Sheets("<")) newsht.Name = Num Next Num End Sub "steven.holloway" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro To Delete/Add & Rename/Arrange Sheets
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 befo=Sheets("<") ActiveSheet.Name = Num End If Next Num End With End Sub "steven.holloway" wrote: 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" wrote: 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(befo=Sheets("<")) newsht.Name = Num Next Num End Sub "steven.holloway" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro To Delete/Add & Rename/Arrange Sheets
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 befo=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" wrote: 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 befo=Sheets("<") ActiveSheet.Name = Num End If Next Num End With End Sub "steven.holloway" wrote: 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" wrote: 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(befo=Sheets("<")) newsht.Name = Num Next Num End Sub "steven.holloway" wrote: 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro To Delete/Add & Rename/Arrange Sheets
See spreadsheet help Excel Specification and limits. the code probably
delete the Numbers worksheets. Make sure the Numbers sheet is not inbetween the < and . "steven.holloway" wrote: 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 befo=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" wrote: 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 befo=Sheets("<") ActiveSheet.Name = Num End If Next Num End With End Sub "steven.holloway" wrote: 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" wrote: 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(befo=Sheets("<")) newsht.Name = Num Next Num End Sub "steven.holloway" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i arrange alphabetically my sheets? | Excel Worksheet Functions | |||
Macro to delete unwanted sheets | Excel Discussion (Misc queries) | |||
Grouped Sheets - Windows Arrange | Excel Discussion (Misc queries) | |||
arrange sheets in alphatical order | New Users to Excel | |||
How do I arrange sheets in alphabetical order? | Excel Discussion (Misc queries) |