|
Deleting original sheets
I have a workbook which has several original sheets of data
Using "TotSheets = Worksheets.Count' I count these sheets and call it "mycount" With a macro I add new sheets. How would I amend the macro at the end to delete the original sheets. 72 years old and just learning so go easy on me. |
Deleting original sheets
One way would be to rename to original sheets, add the new sheets and then
delete all sheets renamed sheets as in this small macro: Sub Deletesheets() Application.ScreenUpdating = False No = 1 For Each sh In Worksheets sh.Name = "DeleteThis" & No No = No + 1 Next sh For x = 1 To 3 Sheets.Add Next x Application.DisplayAlerts = False For Each sh In Worksheets If Left(sh.Name, 10) = "DeleteThis" Then sh.Delete End If Next sh Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Out of curiosity why are you counting the sheets using TotSheets then copying the number to mycount? Why not use mycount in the first place? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Jeff Kelly" wrote in message ... I have a workbook which has several original sheets of data Using "TotSheets = Worksheets.Count' I count these sheets and call it "mycount" With a macro I add new sheets. How would I amend the macro at the end to delete the original sheets. 72 years old and just learning so go easy on me. |
Deleting original sheets
If you dont want to rename the sheets you could send all the names of the sheets as they are when you open the workbook to an array or simply to a list in a worksheet then delete all in the array or list after adding your new sheets, however Sandy has given you a nice simple macro to follow. -- The Code Cage Team Regards, The Code Cage Team www.thecodecage.com ------------------------------------------------------------------------ The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27225 |
Deleting original sheets
If your sheets are added after the last original sheet
then you could just add in a couple of lines like: For i = 1 To myCount Sheets(i).Delete Next If you add the sheets before sheet one then the code would look like. For i = Sheets.Count - myCount To SheetsCount Sheets(i).Delete Next The sheet index number runs from left to right as the tabs appear, regardless of what the tab shows. So you can either start at sheet 1 if all additions were on the back end of the line, or start with the next number after myCount if the sheets were all added to the front end of the line. The only problem would be if they are interspersed or alternated front and rear when added. "Jeff Kelly" wrote: I have a workbook which has several original sheets of data Using "TotSheets = Worksheets.Count' I count these sheets and call it "mycount" With a macro I add new sheets. How would I amend the macro at the end to delete the original sheets. 72 years old and just learning so go easy on me. |
Deleting original sheets
That first suggestion could cause trouble:
For i = 1 To myCount Sheets(1).Delete Next i (Always deleting the first sheet (of the remaining sheets) will be safe.) JLGWhiz wrote: If your sheets are added after the last original sheet then you could just add in a couple of lines like: For i = 1 To myCount Sheets(i).Delete Next If you add the sheets before sheet one then the code would look like. For i = Sheets.Count - myCount To SheetsCount Sheets(i).Delete Next The sheet index number runs from left to right as the tabs appear, regardless of what the tab shows. So you can either start at sheet 1 if all additions were on the back end of the line, or start with the next number after myCount if the sheets were all added to the front end of the line. The only problem would be if they are interspersed or alternated front and rear when added. "Jeff Kelly" wrote: I have a workbook which has several original sheets of data Using "TotSheets = Worksheets.Count' I count these sheets and call it "mycount" With a macro I add new sheets. How would I amend the macro at the end to delete the original sheets. 72 years old and just learning so go easy on me. -- Dave Peterson |
Deleting original sheets
Brilliant, Dave. Works a treat. I promise never to criticise Excel again
+++++++++++++++++++++++ "Dave Peterson" wrote in message ... That first suggestion could cause trouble: For i = 1 To myCount Sheets(1).Delete Next i (Always deleting the first sheet (of the remaining sheets) will be safe.) JLGWhiz wrote: If your sheets are added after the last original sheet then you could just add in a couple of lines like: For i = 1 To myCount Sheets(i).Delete Next If you add the sheets before sheet one then the code would look like. For i = Sheets.Count - myCount To SheetsCount Sheets(i).Delete Next The sheet index number runs from left to right as the tabs appear, regardless of what the tab shows. So you can either start at sheet 1 if all additions were on the back end of the line, or start with the next number after myCount if the sheets were all added to the front end of the line. The only problem would be if they are interspersed or alternated front and rear when added. "Jeff Kelly" wrote: I have a workbook which has several original sheets of data Using "TotSheets = Worksheets.Count' I count these sheets and call it "mycount" With a macro I add new sheets. How would I amend the macro at the end to delete the original sheets. 72 years old and just learning so go easy on me. -- Dave Peterson |
Deleting original sheets
Hi Dave, I am not following you on the logic. If the added sheets are after
the originals, then the original sheets should be indexed 1 To myCount. To delete those original sheets would require starting at sheets(1), ergo For i = 1 To myCount Sheets(i).Delete Next Did I overlook something? "Dave Peterson" wrote: That first suggestion could cause trouble: For i = 1 To myCount Sheets(1).Delete Next i (Always deleting the first sheet (of the remaining sheets) will be safe.) JLGWhiz wrote: If your sheets are added after the last original sheet then you could just add in a couple of lines like: For i = 1 To myCount Sheets(i).Delete Next If you add the sheets before sheet one then the code would look like. For i = Sheets.Count - myCount To SheetsCount Sheets(i).Delete Next The sheet index number runs from left to right as the tabs appear, regardless of what the tab shows. So you can either start at sheet 1 if all additions were on the back end of the line, or start with the next number after myCount if the sheets were all added to the front end of the line. The only problem would be if they are interspersed or alternated front and rear when added. "Jeff Kelly" wrote: I have a workbook which has several original sheets of data Using "TotSheets = Worksheets.Count' I count these sheets and call it "mycount" With a macro I add new sheets. How would I amend the macro at the end to delete the original sheets. 72 years old and just learning so go easy on me. -- Dave Peterson |
Deleting original sheets
Say you have 3 sheets 1,2,3 and add 2 more A,B (in nice order--1,2,3,A,B)
If you use this: For i = 1 To myCount Sheets(i).Delete Next you'll delete sheets(1) and 2,3,A,B will remain Then you'll delete sheets(2). That's 3 (the second one from the left.) So you'll have 2,A,B remaining Then you'll delete sheets(3). That's B. So you'll have 2,A Probably not what you want. JLGWhiz wrote: Hi Dave, I am not following you on the logic. If the added sheets are after the originals, then the original sheets should be indexed 1 To myCount. To delete those original sheets would require starting at sheets(1), ergo For i = 1 To myCount Sheets(i).Delete Next Did I overlook something? "Dave Peterson" wrote: That first suggestion could cause trouble: For i = 1 To myCount Sheets(1).Delete Next i (Always deleting the first sheet (of the remaining sheets) will be safe.) JLGWhiz wrote: If your sheets are added after the last original sheet then you could just add in a couple of lines like: For i = 1 To myCount Sheets(i).Delete Next If you add the sheets before sheet one then the code would look like. For i = Sheets.Count - myCount To SheetsCount Sheets(i).Delete Next The sheet index number runs from left to right as the tabs appear, regardless of what the tab shows. So you can either start at sheet 1 if all additions were on the back end of the line, or start with the next number after myCount if the sheets were all added to the front end of the line. The only problem would be if they are interspersed or alternated front and rear when added. "Jeff Kelly" wrote: I have a workbook which has several original sheets of data Using "TotSheets = Worksheets.Count' I count these sheets and call it "mycount" With a macro I add new sheets. How would I amend the macro at the end to delete the original sheets. 72 years old and just learning so go easy on me. -- Dave Peterson -- Dave Peterson |
Deleting original sheets
Oh yeah, after 1 is gone, 2 becomes 1 and 3 becomes 2, so the second
iteration takes out the original sheets(3) abd skips original sheets(2), etc. Gotcha! What was I thinkin'? "Dave Peterson" wrote: Say you have 3 sheets 1,2,3 and add 2 more A,B (in nice order--1,2,3,A,B) If you use this: For i = 1 To myCount Sheets(i).Delete Next you'll delete sheets(1) and 2,3,A,B will remain Then you'll delete sheets(2). That's 3 (the second one from the left.) So you'll have 2,A,B remaining Then you'll delete sheets(3). That's B. So you'll have 2,A Probably not what you want. JLGWhiz wrote: Hi Dave, I am not following you on the logic. If the added sheets are after the originals, then the original sheets should be indexed 1 To myCount. To delete those original sheets would require starting at sheets(1), ergo For i = 1 To myCount Sheets(i).Delete Next Did I overlook something? "Dave Peterson" wrote: That first suggestion could cause trouble: For i = 1 To myCount Sheets(1).Delete Next i (Always deleting the first sheet (of the remaining sheets) will be safe.) JLGWhiz wrote: If your sheets are added after the last original sheet then you could just add in a couple of lines like: For i = 1 To myCount Sheets(i).Delete Next If you add the sheets before sheet one then the code would look like. For i = Sheets.Count - myCount To SheetsCount Sheets(i).Delete Next The sheet index number runs from left to right as the tabs appear, regardless of what the tab shows. So you can either start at sheet 1 if all additions were on the back end of the line, or start with the next number after myCount if the sheets were all added to the front end of the line. The only problem would be if they are interspersed or alternated front and rear when added. "Jeff Kelly" wrote: I have a workbook which has several original sheets of data Using "TotSheets = Worksheets.Count' I count these sheets and call it "mycount" With a macro I add new sheets. How would I amend the macro at the end to delete the original sheets. 72 years old and just learning so go easy on me. -- Dave Peterson -- Dave Peterson |
Deleting original sheets
OK Jeff, after hashing things out with Dave, the following should do the
trick unless your new pages are interspersed. As long as they are either all before, or all after the originals, one of the two versions below will work. If the new sheets are inserted before the originals: Application.DisplayAlerts = False Dim Sh As Worksheet 'count new sheets x = ThisWorkbook.Sheets.Count - myCount For Each Sh In ThisWorkbook.Sheets If Sh.Index x Then Sh.Delete End If Next Application.DisplayAlerts = True If the new sheets are inserted after the originals: Application.DisplayAlerts = False Dim Sh As Worksheet For Each Sh In ThisWorkbook.Sheets If Sh.Index <= myCount Then Sh.Delete End If Next Application.DisplayAlerts = True "Jeff Kelly" wrote: I have a workbook which has several original sheets of data Using "TotSheets = Worksheets.Count' I count these sheets and call it "mycount" With a macro I add new sheets. How would I amend the macro at the end to delete the original sheets. 72 years old and just learning so go easy on me. |
Deleting original sheets
Or you could just start with the rightmost original sheet and delete the sheets
to the left. For i = myCount to 1 step -1 Sheets(i).Delete Next i JLGWhiz wrote: OK Jeff, after hashing things out with Dave, the following should do the trick unless your new pages are interspersed. As long as they are either all before, or all after the originals, one of the two versions below will work. If the new sheets are inserted before the originals: Application.DisplayAlerts = False Dim Sh As Worksheet 'count new sheets x = ThisWorkbook.Sheets.Count - myCount For Each Sh In ThisWorkbook.Sheets If Sh.Index x Then Sh.Delete End If Next Application.DisplayAlerts = True If the new sheets are inserted after the originals: Application.DisplayAlerts = False Dim Sh As Worksheet For Each Sh In ThisWorkbook.Sheets If Sh.Index <= myCount Then Sh.Delete End If Next Application.DisplayAlerts = True "Jeff Kelly" wrote: I have a workbook which has several original sheets of data Using "TotSheets = Worksheets.Count' I count these sheets and call it "mycount" With a macro I add new sheets. How would I amend the macro at the end to delete the original sheets. 72 years old and just learning so go easy on me. -- Dave Peterson |
Deleting original sheets
Yep. You're correct.
Sandy Mann wrote: JLGWhiz" wrote in message ... If the new sheets are inserted after the originals: Application.DisplayAlerts = False Dim Sh As Worksheet For Each Sh In ThisWorkbook.Sheets If Sh.Index <= myCount Then Sh.Delete End If Next Application.DisplayAlerts = True Dosen't that suffer from the same type problem as the original cpde? For me it deletes all sheets up to the last and then errors out because it is trying to delete the last sheet. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "JLGWhiz" wrote in message ... OK Jeff, after hashing things out with Dave, the following should do the trick unless your new pages are interspersed. As long as they are either all before, or all after the originals, one of the two versions below will work. If the new sheets are inserted before the originals: Application.DisplayAlerts = False Dim Sh As Worksheet 'count new sheets x = ThisWorkbook.Sheets.Count - myCount For Each Sh In ThisWorkbook.Sheets If Sh.Index x Then Sh.Delete End If Next Application.DisplayAlerts = True If the new sheets are inserted after the originals: Application.DisplayAlerts = False Dim Sh As Worksheet For Each Sh In ThisWorkbook.Sheets If Sh.Index <= myCount Then Sh.Delete End If Next Application.DisplayAlerts = True "Jeff Kelly" wrote: I have a workbook which has several original sheets of data Using "TotSheets = Worksheets.Count' I count these sheets and call it "mycount" With a macro I add new sheets. How would I amend the macro at the end to delete the original sheets. 72 years old and just learning so go easy on me. -- Dave Peterson |
| All times are GMT +1. The time now is 12:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com