for Each worksheet error/bug

[ag273n]

i'm creating a file that is supposed to do all the manual work i need.. it has one constant sheet "GMTdata" and other sheets are transient..

now my problem is this macro:

Please Login or Register  to view this content.

that macro above is supposed to redo the same macro for all other sheets except the sheet name "GMTdata". Now to prevent problems with macros doing unwanted stuff on the GMTdata sheet, i added a code that would exit the sub in case the active sheet is the GMTdata sheet along with a mesage box.

so the macro above will call this other macro:

Please Login or Register  to view this content.

now that macro will call all other macros to perform specific tasks on the worksheet... i had to do it this way so its easier to edit it in the future, in case...
notice the first part of the sub A01_ProcessData has an exit sub statement and a mesage box in case the active sheet is the GMTdata...

so i tried running the Sub SynthesizeDuplicates_Click but the end result is that, it does nothing, and it just keeps popping the prompt i created, which means either that it has not moved away from the GMTdata sheet, or it has been skipping the if conditions i added...

any better ideas??

[GC Excel]

Is this line really commented in your code? If yes it is not executed. If your first sheet is "GMT Data", the macro will immediately stop.

Please Login or Register  to view this content.

[GC Excel]

In fact, the problem is because you use ActiveSheet

Please Login or Register  to view this content.

When you loop through all sheets, the ActiveSheet always remains the first sheet ("GMT Data" in your case)

You could either activate the sheet before calling A01_ProcessData or pass the sheet as an argument in this function

Example

Please Login or Register  to view this content.

and in your main procedure use :

Please Login or Register  to view this content.

[ag273n]

on your first reply, its not as a comment. i was playing around with it see if there's some way i could make it work.. the message box on that code was a test i tried to see if it can detect all other sheets except the GMTdata sheet.. well, that test worked.. but when i add the actual "call sub" statement... its stops working..

i cannot activate another sheet given that all other sheets are transient and can be deleted.. unless if there's a way to activate another sheet next to the GMTdata sheet without having to reference the name of the sheet...


btw, fyi, the first sub "SynthesizeDuplicates_Click" is on the GMTdata module, while the other sub "A01_ProcessData" is on a separate module I inserted.

the second option that you were saying "or pass the sheet as an argument in this function"
how do i do that?

[ag273n]

now I tried what you recommended adding

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

and it returned:

"Compile Error:

Procedure declaration does not match description of event or procedure having the same name"

even before I could run the macro...
maybe because the two subs are located on separate modules.. is that the problem?

[shyamhappy]

Please Login or Register  to view this content.

Please try this code.


___________________________________________________________________________________________________________________________________________
if you feel something has helped, please click * for reputation

“I have not failed. I've just found 10,000 ways that won't work.”
― Thomas A. Edison

[ag273n]

I tried that, and sadly, it still didn't work...
I might as well attach a sample file here for you to try it..

TEST_TEMPLATE(modified)V6.1 - Copy.xlsm

[GC Excel]

The code (VBA Project) is password protected... difficult to help.

[ag273n]

woops...
sorry..

this other file should be easier to access..

TEST_TEMPLATE(modified)V6.1 - Copy.xlsm

[GC Excel]

Made 2 smalls changes and it seems to work. Please verify...

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[ag273n]

I am totally amazed... it worked like magic!... thanks a bunch! finally!