Code Gives error if I run it from Module but works fine when run it from This Worksheet

**VKS** · 05-17-2013, 05:25 AM

Hello Experts,
I have a code below to delete few unwanted sheets and keep the sheets where I have SMRY as a part of the sheet name. This code works fine if I put it in This Worksheet. I get runtime error if I put it in module and try to operate the code from there. Tried changing the name to Sub XXX still got the error.
From module it gets stuck at the line below

Please Login or Register  to view this content.

and the full code is

Please Login or Register  to view this content.

Please advise. Best Regards/VKS

**cytop** · 05-17-2013, 05:34 AM

Syntactically the code is correct, and about the only error that it can raise is

Method 'Delete' of object '_Worksheet' failed

Which means it's trying to delete the last sheet. A workbook must have a least 1 worksheet...

**:) Sixthsense :)** · 05-17-2013, 05:38 AM

Code seems to be perfect no errors found at my end

**VKS** · 05-17-2013, 05:58 AM

Thanks for your time & Help Cytop & Sixthsense,
The workbook has about 275 tabs out of which only 50 to 60 are SMRY (summary sheets) so there is no way that there is no worksheet (Not even hidden).
Though i have my way around (paste the code in this workbook only) but it makes me think coz a devil sends me such a file every month.
Best Regards/VKS
On second thoughts do u think merged cells and hidden columns can effect the performance (but then they are there when you paste the code in this workbook also)..

**:) Sixthsense :)** · 05-17-2013, 06:04 AM

If possible close that workbook and run the code on a new workbook by keeping it in a module and confirm

**Norie** · 05-17-2013, 06:07 AM

When you get the error what does this return when you try it in the Immediate Window(CTRL+G)?

Please Login or Register  to view this content.

**VKS** · 05-17-2013, 06:35 AM

Originally Posted by Norie

When you get the error what does this return when you try it in the Immediate Window(CTRL+G)?

Please Login or Register  to view this content.

Surprisingly 1 but count is 275

**Norie** · 05-17-2013, 06:40 AM

That means, as cytop suggested, that the code is trying to delete all the worksheets.

I think you might need to double check the sheet names.

**VKS** · 05-17-2013, 07:05 AM

Originally Posted by Norie

That means, as cytop suggested, that the code is trying to delete all the worksheets.

I think you might need to double check the sheet names.

I open the worksheet again ensuring only one workbook is open
Then I go to immediate window and do
?thisworkbook.Worksheets.Count
275
Then I open the module run the code and get runtime error 1004
I go back to immediate window and do
?thisworkbook.Worksheets.Count
1 again.
Something is surely wrong in running the code coz immediate window before running the code shows 275 and after running the code its same immediate window shows 1
After getting 1 I go to first sheet called summary sheet right click and open view code then go to vba project and select Thisworkbook at the bottom and then paste the code hit F5 and do a control G and type ?thisworkbook.Worksheets.Count
50 is what I get
I agree that code is wrong but I don’t think its deleting all the sheets.

**VKS** · 05-17-2013, 07:29 AM

Dear Friends,
John H Davis on this forum gave me a the code below which ran very well from the module when i opend the file first hence sharing (Will try it cpl of times more) before marking this thread as solved. Thanks John.

Please Login or Register  to view this content.

**:) Sixthsense :)** · 05-17-2013, 07:36 AM

Little bit of changes in your post #10 code

Please Login or Register  to view this content.

**Norie** · 05-17-2013, 08:26 AM

Are you sure the code is running on the correct workbook?

ThisWorkbook refers to the workbook the code is in.

**VKS** · 05-17-2013, 01:18 PM

@ Norie: I ensured other work books were closed.
@ Norie, Sixthsense & cytop : Thanks for your time and help you all are stars and have my stars
Since I have my way around and a new code i think i should mark this thread as solved
Thanks once again

Code Gives error if I run it from Module but works fine when run it from This Worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Code Gives error if I run it from Module but works fine when run it from This Worksheet

Re: Code Gives error if I run it from Module but works fine when run it from This Workshee

Re: Code Gives error if I run it from Module but works fine when run it from This Workshee

Re: Code Gives error if I run it from Module but works fine when run it from This Workshee

Re: Code Gives error if I run it from Module but works fine when run it from This Workshee

Re: Code Gives error if I run it from Module but works fine when run it from This Workshee

Re: Code Gives error if I run it from Module but works fine when run it from This Workshee

Re: Code Gives error if I run it from Module but works fine when run it from This Workshee

Re: Code Gives error if I run it from Module but works fine when run it from This Workshee

Re: Code Gives error if I run it from Module but works fine when run it from This Workshee

Re: Code Gives error if I run it from Module but works fine when run it from This Workshee

Re: Code Gives error if I run it from Module but works fine when run it from This Workshee

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions