Home |
Search |
Today's Posts |
#1
|
|||
|
|||
not delete worksheets from names in a range
Is there anyway to delete worksheets where one worksheet contains a
range with the names of worksheets I DONT want to delete? i.e I want to have a code that says "do not delete worksheets in the range but delete all other worksheets" |
#2
|
|||
|
|||
not delete worksheets from names in a range
You can create a subroutine in vba like this:
Sub delete() 'Tell Excel to not prompt the deletion confirmation Application.DisplayAlerts = False For Each sh In ThisWorkbook.Sheets forDeletion = True ' Sheet1.Range("A1:A3") refers to the range of cells that ' hold the sheet needs we don't want to delete. You're better ' off using a named range to refer to those. For Each n In Sheet1.Range("A1:A3") If sh.Name = n.Value Then forDeletion = False Exit For End If Next n If forDeletion Then sh.delete End If Next sh End Sub "DARREN FONG" wrote: Is there anyway to delete worksheets where one worksheet contains a range with the names of worksheets I DONT want to delete? i.e I want to have a code that says "do not delete worksheets in the range but delete all other worksheets" |
#3
|
|||
|
|||
not delete worksheets from names in a range
One way:
Sub DelShts() Dim rng() As Variant Dim i As Integer Dim ans As Variant Application.DisplayAlerts = False rng = Range("A2:A6") For i = Sheets.Count To 1 Step -1 On Error Resume Next ans = Application.WorksheetFunction. _ Match(Sheets(i).Name, rng, 0) If ans = Empty Then Sheets(i).Delete ans = Empty On Error GoTo 0 Next i Application.DisplayAlerts = True End Sub Hope this helps Rowan DARREN FONG wrote: Is there anyway to delete worksheets where one worksheet contains a range with the names of worksheets I DONT want to delete? i.e I want to have a code that says "do not delete worksheets in the range but delete all other worksheets" |
#4
|
|||
|
|||
not delete worksheets from names in a range
Jason,
Thanks for your help, however it does'nt seem to work I think this is because "n" is not defined, I've tried a few things but nothing works as yet Cheers Daz Jason Clement wrote: You can create a subroutine in vba like this: Sub delete() 'Tell Excel to not prompt the deletion confirmation Application.DisplayAlerts = False For Each sh In ThisWorkbook.Sheets forDeletion = True ' Sheet1.Range("A1:A3") refers to the range of cells that ' hold the sheet needs we don't want to delete. You're better ' off using a named range to refer to those. For Each n In Sheet1.Range("A1:A3") If sh.Name = n.Value Then forDeletion = False Exit For End If Next n If forDeletion Then sh.delete End If Next sh End Sub Is there anyway to delete worksheets where one worksheet contains a range with the names of worksheets I DONT want to delete? i.e I want to have a code that says "do not delete worksheets in the range but delete all other worksheets" -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200511/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel how do I delete or change the name of a range of cells? | Excel Discussion (Misc queries) | |||
Deleting Range name's listed in the range address box. | Excel Discussion (Misc queries) | |||
range names | Excel Worksheet Functions | |||
SUMIF across a range of worksheets | Excel Worksheet Functions | |||
range names | Excel Worksheet Functions |