Delete last sheet in workbook

[Joe Gieder]

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

 

[PCLIVE]

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

 

[Joe Gieder]

Thanks for the reply. Sometimes it's in the middle not very often but it's
always called "Sheet X".

Thanks
Joe

 

[PCLIVE]

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

--

 

[Joe Gieder]

X is an unknown.

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

 

[Joe Gieder]

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

 

[Joe Gieder]

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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

 

[Joe Gieder]

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 Gieder]

Paul,
Thanks for your help. Your solution worked perfectly.

Joe

 

[Bernie Deitrick]

Joe,

Change

Worksheets.Add.Name = shtName

To

Worksheets.Add(, Worksheets(Worksheets.Count)).Name = shtName

HTH,
Bernie
MS Excel MVP

 

[Joe Gieder]

Worked perfectly. Thank you so much for your help.

Joe

 

[Bernie Deitrick]

Worked perfectly. Thank you so much for your help.

You're very welcome.

Bernie
MS Excel MVP