Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

delete mutiple workseets 1

Status
Not open for further replies.

binway

MIS
Nov 9, 2003
21
AU
I have a macro that cycles through some data and creates worksheets named with the data. After I have created the sheets I want to delete them and keep the ones named so that it is ready for the next cycle.

The code I have
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Sheets
If wks.Name <> "Data" And wks.Name <> "Summary" And wks.Name <> "Tab1" Then wks.Delete
Next wks
I get the error
run time error 1004
method delete of object worksheet failed
 
Hi,
Code:
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Sheets
  Select Case wks.name
    Case "Data","Summary","Tab1"
    Case Else
       Wks.Delete
  End Select
Next wks

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Thanks Skip
I actually went and pinched some code you had sent me previously about reading data in columns and reused that with delete function.
For Each rec In .Range(c, c.End(xlDown))
wks = rec.Value
Set ws = Sheets(wks)
ws.Delete
Next
 
Code:
For Each rec In .Range(c, c.End(xlDown))
   Sheets(rec.Value).Delete
Next
Better have the spelling correct on each value!

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top