LinkBack URL
About LinkBacks
Hello, I am wondering if anyone knows how to delete the last X number of sheets from a workbook via VBA? I need a code like this at the end of one of my macros.
Thank you!
Hello, I am wondering if anyone knows how to delete the last X number of sheets from a workbook via VBA? I need a code like this at the end of one of my macros.
Thank you!
Last edited by kirsty; 12-04-2010 at 12:35 AM.
Hi kirsty,
When Excel adds a worksheet it gives it an index number. You can see this from the immediate window by typing:
and press enter. It will show how many worksheets are in the open workbook.
Please Login or Register to view this content.
Then try:
and press enter. The name of the 3rd (index) will be displayed in the immediate window.Please Login or Register to view this content.
Lets say you have 22 worksheets in a workbook and want to delete that last 10. You could write VBA code like:
I hope that is enough to have you succeed.Please Login or Register to view this content.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi Kirsty,
The following code will delete the last three sheets from a workbook which contains this routine:
Please Login or Register to view this content.
Hope this helps - please let me know how you get on.
Regards,
Greg M
Thank you very much for the responses!
MarvinP - I tried your short code first but it returned an error saying I must define an object? Because I am extremely new at this, I did not attempt to troubleshoot and moved onto the longer code posted by Greg M. Thank you for the information regarding how to obtain worksheet info from Excel; every bit of information helps me a lot to understand what I'm doing.
Greg M - Your code worked perfectly to delete the last two sheets. I can now add it to the end of my macro and it will be nearly perfect. Thank you so much!
And yet a third option for you, Kirsty. It should handle incorrect sheet names or invalid number of sheets.
Please Login or Register to view this content.
Hi Kirsty,
Many thanks for your prompt feedback - I'm glad I was able to help.
Greg M
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks