| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Worksheets Named Sheet1, Sheet2, Sheet3, etc.
Some great people helped me out with code similar to that posted below. I
used a slightly modified version of this code to delete a few specific sheets, and I am now trying to modify it to delete any sheet named Sheet1, Sheet2, Sheet3, etc. Basically, I have code that loops and builds a workbook with many sheets that contain information about stocks. Sometimes the loop spits out sheets named Sheet1, Sheet2, etc. and then it ends. Im trying to find a way to delete these few sheets that are created. I just cant figure out why the samples of code below will not delete the superfluous sheets. Does VBA support the * character? Sub DelSheets() For Each ws In Worksheets If ws.Name = "Sheet*" Then ws.Delete Next ws For Each ws In Workbooks If ws.Name Like "Sheet*" Then ws.Delete Next ws For Each sh In ActiveWorkbook.Worksheets If InStr(1, sh.Name, "Sheet*") Then sh.Delete End If Next sh End Sub -- RyGuy |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Worksheets Named Sheet1, Sheet2, Sheet3, etc.
i assume you have other worksheets in the workbook that AREN'T named
sheet*??? because you can't delete all the worksheets in a workbook. you have to have at least one there, and at least one visible. hth susan On Apr 6, 2:52 pm, ryguy7272 wrote: Some great people helped me out with code similar to that posted below. I used a slightly modified version of this code to delete a few specific sheets, and I am now trying to modify it to delete any sheet named Sheet1, Sheet2, Sheet3, etc. Basically, I have code that loops and builds a workbook with many sheets that contain information about stocks. Sometimes the loop spits out sheets named Sheet1, Sheet2, etc. and then it ends. I'm trying to find a way to delete these few sheets that are created. I just can't figure out why the samples of code below will not delete the superfluous sheets. Does VBA support the * character? Sub DelSheets() For Each ws In Worksheets If ws.Name = "Sheet*" Then ws.Delete Next ws For Each ws In Workbooks If ws.Name Like "Sheet*" Then ws.Delete Next ws For Each sh In ActiveWorkbook.Worksheets If InStr(1, sh.Name, "Sheet*") Then sh.Delete End If Next sh End Sub -- RyGuy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Worksheets Named Sheet1, Sheet2, Sheet3, etc.
Sub DeleteSheets()
Dim aWS As Worksheet For Each aWS In ActiveWorkbook.Worksheets Debug.Print aWS.Name If aWS.Name Like "Sheet*" Then aWS.Delete End If Next aWS End Sub "ryguy7272" wrote: Some great people helped me out with code similar to that posted below. I used a slightly modified version of this code to delete a few specific sheets, and I am now trying to modify it to delete any sheet named Sheet1, Sheet2, Sheet3, etc. Basically, I have code that loops and builds a workbook with many sheets that contain information about stocks. Sometimes the loop spits out sheets named Sheet1, Sheet2, etc. and then it ends. Im trying to find a way to delete these few sheets that are created. I just cant figure out why the samples of code below will not delete the superfluous sheets. Does VBA support the * character? Sub DelSheets() For Each ws In Worksheets If ws.Name = "Sheet*" Then ws.Delete Next ws For Each ws In Workbooks If ws.Name Like "Sheet*" Then ws.Delete Next ws For Each sh In ActiveWorkbook.Worksheets If InStr(1, sh.Name, "Sheet*") Then sh.Delete End If Next sh End Sub -- RyGuy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Worksheets Named Sheet1, Sheet2, Sheet3, etc.
Yes, I have several other sheets in the workbook. I am trying to delete only
two or three that come in at the end of the loop. These irrelevant sheets are always named Sheet1, Sheet2, etc. Sometimes Sheet18, Sheet19, etc. will be crated if I run the loop several times before closing the workbook. -- RyGuy "Susan" wrote: i assume you have other worksheets in the workbook that AREN'T named sheet*??? because you can't delete all the worksheets in a workbook. you have to have at least one there, and at least one visible. hth susan On Apr 6, 2:52 pm, ryguy7272 wrote: Some great people helped me out with code similar to that posted below. I used a slightly modified version of this code to delete a few specific sheets, and I am now trying to modify it to delete any sheet named Sheet1, Sheet2, Sheet3, etc. Basically, I have code that loops and builds a workbook with many sheets that contain information about stocks. Sometimes the loop spits out sheets named Sheet1, Sheet2, etc. and then it ends. I'm trying to find a way to delete these few sheets that are created. I just can't figure out why the samples of code below will not delete the superfluous sheets. Does VBA support the * character? Sub DelSheets() For Each ws In Worksheets If ws.Name = "Sheet*" Then ws.Delete Next ws For Each ws In Workbooks If ws.Name Like "Sheet*" Then ws.Delete Next ws For Each sh In ActiveWorkbook.Worksheets If InStr(1, sh.Name, "Sheet*") Then sh.Delete End If Next sh End Sub -- RyGuy |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Worksheets Named Sheet1, Sheet2, Sheet3, etc.
This will delete the sheets with SHeet* without a dialog box
Sub DeleteSheets() Dim aWS As Worksheet For Each aWS In ActiveWorkbook.Worksheets Debug.Print aWS.Name If aWS.Name Like "Sheet*" Then Application.DisplayAlerts = False aWS.Delete Application.DisplayAlerts = True End If Next aWS End Sub "ryguy7272" wrote: Yes, I have several other sheets in the workbook. I am trying to delete only two or three that come in at the end of the loop. These irrelevant sheets are always named Sheet1, Sheet2, etc. Sometimes Sheet18, Sheet19, etc. will be crated if I run the loop several times before closing the workbook. -- RyGuy "Susan" wrote: i assume you have other worksheets in the workbook that AREN'T named sheet*??? because you can't delete all the worksheets in a workbook. you have to have at least one there, and at least one visible. hth susan On Apr 6, 2:52 pm, ryguy7272 wrote: Some great people helped me out with code similar to that posted below. I used a slightly modified version of this code to delete a few specific sheets, and I am now trying to modify it to delete any sheet named Sheet1, Sheet2, Sheet3, etc. Basically, I have code that loops and builds a workbook with many sheets that contain information about stocks. Sometimes the loop spits out sheets named Sheet1, Sheet2, etc. and then it ends. I'm trying to find a way to delete these few sheets that are created. I just can't figure out why the samples of code below will not delete the superfluous sheets. Does VBA support the * character? Sub DelSheets() For Each ws In Worksheets If ws.Name = "Sheet*" Then ws.Delete Next ws For Each ws In Workbooks If ws.Name Like "Sheet*" Then ws.Delete Next ws For Each sh In ActiveWorkbook.Worksheets If InStr(1, sh.Name, "Sheet*") Then sh.Delete End If Next sh End Sub -- RyGuy |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Worksheets Named Sheet1, Sheet2, Sheet3, etc.
Excellent!! That's exactly it!! How come my version did not work Bara? Did
it have something to do with the ".Name Like "Sheet*" Then"? -- RyGuy "Barb Reinhardt" wrote: Sub DeleteSheets() Dim aWS As Worksheet For Each aWS In ActiveWorkbook.Worksheets Debug.Print aWS.Name If aWS.Name Like "Sheet*" Then aWS.Delete End If Next aWS End Sub "ryguy7272" wrote: Some great people helped me out with code similar to that posted below. I used a slightly modified version of this code to delete a few specific sheets, and I am now trying to modify it to delete any sheet named Sheet1, Sheet2, Sheet3, etc. Basically, I have code that loops and builds a workbook with many sheets that contain information about stocks. Sometimes the loop spits out sheets named Sheet1, Sheet2, etc. and then it ends. Im trying to find a way to delete these few sheets that are created. I just cant figure out why the samples of code below will not delete the superfluous sheets. Does VBA support the * character? Sub DelSheets() For Each ws In Worksheets If ws.Name = "Sheet*" Then ws.Delete Next ws For Each ws In Workbooks If ws.Name Like "Sheet*" Then ws.Delete Next ws For Each sh In ActiveWorkbook.Worksheets If InStr(1, sh.Name, "Sheet*") Then sh.Delete End If Next sh End Sub -- RyGuy |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Worksheets Named Sheet1, Sheet2, Sheet3, etc.
First, you have to declare ws (or aws) as a worksheet
And you need For each aws in Activeworkbook.worksheets I don't think you had that in some. "ryguy7272" wrote: Excellent!! That's exactly it!! How come my version did not work Bara? Did it have something to do with the ".Name Like "Sheet*" Then"? -- RyGuy "Barb Reinhardt" wrote: Sub DeleteSheets() Dim aWS As Worksheet For Each aWS In ActiveWorkbook.Worksheets Debug.Print aWS.Name If aWS.Name Like "Sheet*" Then aWS.Delete End If Next aWS End Sub "ryguy7272" wrote: Some great people helped me out with code similar to that posted below. I used a slightly modified version of this code to delete a few specific sheets, and I am now trying to modify it to delete any sheet named Sheet1, Sheet2, Sheet3, etc. Basically, I have code that loops and builds a workbook with many sheets that contain information about stocks. Sometimes the loop spits out sheets named Sheet1, Sheet2, etc. and then it ends. Im trying to find a way to delete these few sheets that are created. I just cant figure out why the samples of code below will not delete the superfluous sheets. Does VBA support the * character? Sub DelSheets() For Each ws In Worksheets If ws.Name = "Sheet*" Then ws.Delete Next ws For Each ws In Workbooks If ws.Name Like "Sheet*" Then ws.Delete Next ws For Each sh In ActiveWorkbook.Worksheets If InStr(1, sh.Name, "Sheet*") Then sh.Delete End If Next sh End Sub -- RyGuy |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Worksheets Named Sheet1, Sheet2, Sheet3, etc.
Ahhhh! I read your response, reviewed my post, and realized that you are
correct. I didnt have a Dim ws As Worksheet. Thanks so much!! -- RyGuy "Barb Reinhardt" wrote: First, you have to declare ws (or aws) as a worksheet And you need For each aws in Activeworkbook.worksheets I don't think you had that in some. "ryguy7272" wrote: Excellent!! That's exactly it!! How come my version did not work Bara? Did it have something to do with the ".Name Like "Sheet*" Then"? -- RyGuy "Barb Reinhardt" wrote: Sub DeleteSheets() Dim aWS As Worksheet For Each aWS In ActiveWorkbook.Worksheets Debug.Print aWS.Name If aWS.Name Like "Sheet*" Then aWS.Delete End If Next aWS End Sub "ryguy7272" wrote: Some great people helped me out with code similar to that posted below. I used a slightly modified version of this code to delete a few specific sheets, and I am now trying to modify it to delete any sheet named Sheet1, Sheet2, Sheet3, etc. Basically, I have code that loops and builds a workbook with many sheets that contain information about stocks. Sometimes the loop spits out sheets named Sheet1, Sheet2, etc. and then it ends. Im trying to find a way to delete these few sheets that are created. I just cant figure out why the samples of code below will not delete the superfluous sheets. Does VBA support the * character? Sub DelSheets() For Each ws In Worksheets If ws.Name = "Sheet*" Then ws.Delete Next ws For Each ws In Workbooks If ws.Name Like "Sheet*" Then ws.Delete Next ws For Each sh In ActiveWorkbook.Worksheets If InStr(1, sh.Name, "Sheet*") Then sh.Delete End If Next sh End Sub -- RyGuy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simple? View named range from sheet1 on sheet2 | Excel Worksheet Functions | |||
| copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
| A1 in sheet1 =” =SUM('sheet2:sheet3'!A1)” | Excel Programming | |||
| consoildate all the worksheet(example sheet1,sheet2 and sheet3 etc | Excel Worksheet Functions | |||
| copy data from sheet1 based on criteria in sheet2 to sheet3 | Excel Programming | |||
Linear Mode
