 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008Delete last sheet in workbook
First, thank you for looking at this post. I have a macro that takes the cell comments from other sheets and creates a summary sheet showing all of them. I'm constantly adding to the main sheets and running this macro but every time I do I have to first delete the sheet the macro created. Since the macro is called different tings every time it's created I just say delete sheet xx. Since 95% of the time this sheet is at the end is there any code that will automatically find and delete this sheet, the sheet is always called "Sheet?". Also is there any code that I can use so I don't have to hit OK when the message box pops up saying that the sheet will permenantly be deleted? Thanks again for you help Joe Use this ONLY if it is always the last sheet in your workbook. Application.DisplayAlerts = False Sheets(Sheets.Count).Delete Application.DisplayAlerts = True HTH, Paul > First, thank you for looking at this post. > I have a macro that takes the cell comments from other sheets and creates [quoted text clipped - 15 lines] > Thanks again for you help > Joe Thanks for the reply. Sometimes it's in the middle not very often but it's always called "Sheet X". Thanks Joe > Use this ONLY if it is always the last sheet in your workbook. > [quoted text clipped - 24 lines] > > Thanks again for you help > > Joe Does "X" represent an unknown number, or is it actually "SheetX" or "Sheet X"? If that is the case, then I would think you could just adjust the code to include the specific sheet name. Sheets("SheetX").Delete If "SheetX" is not the actual sheet name and "X" just represents the automatic sheet number when a sheet is inserted, then we'll have to come up with an alternative. If that is the case, then how many sheets could there be in this workbook? Would the X number ever be double digits? Would any other sheet names end in a number (example, a sheet named say, "Test4"...basically a sheet that does not begin with the word "Sheet" but still ends in a number). Regards, Paul > Thanks for the reply. Sometimes it's in the middle not very often but it's > always called "Sheet X". [quoted text clipped - 34 lines] >> > Thanks again for you help >> > Joe X is an unknown. > Does "X" represent an unknown number, or is it actually "SheetX" or "Sheet > X"? [quoted text clipped - 52 lines] > >> > Thanks again for you help > >> > Joe Sorry. I did not read the whole question. X is the automatic sheet number and it should not be double digits. I currently have six named sheets but I will be adding several mor to consolidate workbooks. Yes, other sheets do end in a number because they're purchase order numbers. Thanks for your help Joe > Does "X" represent an unknown number, or is it actually "SheetX" or "Sheet > X"? [quoted text clipped - 52 lines] > >> > Thanks again for you help > >> > Joe I found this code: Sub DeleteSheets() Dim wks As Worksheet On Error GoTo ErrorHandler Application.DisplayAlerts = False For Each wks In Worksheets If InStrUCase(wks.Name, "Bill") > 0 Then wks.Delete Next wks ErrorHandler: Application.DisplayAlerts = True End Sub And replaced Bill with Sheet but it did not work, I got an error saying "Sub or Function not defined" and I'm not sure what it's telling me. Joe > Does "X" represent an unknown number, or is it actually "SheetX" or "Sheet > X"? [quoted text clipped - 52 lines] > >> > Thanks again for you help > >> > Joe Joe, It is telling you that InStrUCase is a custom function that you haven't included in your module. See my other response for the commonly accepted best technique for doing what you want to do. HTH, Bernie MS Excel MVP >I found this code: > Sub DeleteSheets() [quoted text clipped - 71 lines] >> >> > Thanks again for you help >> >> > Joe Paul, Thanks for your help. Your solution worked perfectly. Joe > Use this ONLY if it is always the last sheet in your workbook. > [quoted text clipped - 24 lines] > > Thanks again for you help > > Joe Joe, In your macro that creates the sheet, first delete the sheet before you try adding a new one, and cotrol the sheet name. Dim myS As Worksheet Dim shtName As String shtName = "Comments Summary" On Error Resume Next Application.DisplayAlerts = False Worksheets(shtName).Delete Application.DisplayAlerts = True Worksheets.Add.Name = shtName 'Other code to put comments on sheet HTH, Bernie MS Excel MVP > First, thank you for looking at this post. > I have a macro that takes the cell comments from other sheets and creates a [quoted text clipped - 9 lines] > Thanks again for you help > Joe Thanks Bernie. I was just using the default worksheet name (Sheet) originally but when I changed the code to make the sheet name "Comments Summary" your code worked perfectly. Just one more question, how can I get the new sheet to be inserted as the last sheet. It's currently created one before the last. Thank you for all your help and expertise Joe > Joe, > [quoted text clipped - 31 lines] > > Thanks again for you help > > Joe Joe, Change Worksheets.Add.Name = shtName To Worksheets.Add(, Worksheets(Worksheets.Count)).Name = shtName HTH, Bernie MS Excel MVP > Thanks Bernie. I was just using the default worksheet name (Sheet) originally > but when I changed the code to make the sheet name "Comments Summary" your [quoted text clipped - 41 lines] >> > Thanks again for you help >> > Joe Worked perfectly. Thank you so much for your help. Joe > Joe, > [quoted text clipped - 55 lines] > >> > Thanks again for you help > >> > Joe > Worked perfectly. Thank you so much for your help. You're very welcome. Bernie MS Excel MVP |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.