Macro runs well but not when called by another ??

[james 35]

Guys below is a macro I've managed to scrape together which does the following
*
Unhides all hidden sheets within the work book
Copies a Sheets array to other open WB's
Then hides the sheets again in the original WB
The primary macro which isnt shown generates separate WB's one at a time and the names cycle Book1 Book 2 etc...
*
the issue I have is that the Backit2 () sub below works well as a standalone i.e. selected and run from the VBA toolbar however when I add to the main macro and tell it to call Backit2 () it fails at the sheets array section!
*
So works fine alone but fails when called lol :-/
*
I am baffled
*
If any one could take a look through the code and advise where I'm going wrong and any ineficiences that would be great !
*
in addition if anyone could advise how to get the sheets to copy only to open excel WB name begining with "book" followed by a number i.e. book 1 book 2 etc. I would be very greatful!!
*
Macro below
*

Please Login or Register  to view this content.

Sorry about the * struggle to post from works PC and for some reason on my mobile it always puts the * in lol

[RHCPgergo]

Hi james,

I think including a full workbook reference will solve your problem.

Please Login or Register  to view this content.

[james 35]

Hi james,

I think including a full workbook reference will solve your problem.

Please Login or Register  to view this content.

Thanks RHCP but unfortunately no... just altered it to this

Please Login or Register  to view this content.

But with no Joy :-/

Any other ideas ?

Dont know if I mentioned but its a runtime error 9 ??

thanks again

[RHCPgergo]

Is it possible that one of the worksheets has a different name? Or all of these are correct?

Please Login or Register  to view this content.

[james 35]

Is it possible that one of the worksheets has a different name? Or all of these are correct?

Please Login or Register  to view this content.

I'm pretty sure theyre all correct, the thing thats funny is that several macro's get called and if I run it from the second macro it runs just fine ! Just not running it as one on the whole :-/

Will post more of coding up !:-)

This is the end of the first main macro -

Please Login or Register  to view this content.

Which leads to

Please Login or Register  to view this content.

Which leads to

Please Login or Register  to view this content.

Have just checked WS names and yes theyre all correct shouldnt have really as it started as a recorded macro lol :-)

[RHCPgergo]

Not sure really, but maybe this Procedure runs on a different workbook?

Please Login or Register  to view this content.

I mean, maybe you are unhiding worksheets on another workbook and later you can't access the ones you really want, because they are hidden. Just an idea, I'm not really sure...


edit: if this is the case, you should specify in which workbook you want to unhide the worksheets.

Please Login or Register  to view this content.

[bstier]

I am not 100 percent sure but it sounds like this might be a variable problem. Variables are a great tool but when you call a procedure and want that variable to be used by another procedure you have to make that variable public at the beginning of the main vba code.

So before your main code starts you would type for example

Please Login or Register  to view this content.

[james 35]

This is really weird guys !

just made some alterations to the sheets to hide part as it refused to recognise a sheet to hide despite double checking name etc. and ended up just recording an extra macro that it calls lol

Anyway back to the original issue I've tried the Public ww as worksheet suggested by bstier with no difference..

So what I now have That works when ran from workSheetsShow

Please Login or Register  to view this content.

But for the life of me I can't get it to work correctly when Worsheetshow is called from the original macro that creates the new WB's??

Any Idea's as I'm going bald from pulling my hair out lol

james

[RHCPgergo]

Hi james,

have you tried my suggestion in comment #6?
If you don't reference a workbook fully, it will always look at the active workbook, which is not always what you want.

[james 35]

Hi james,

have you tried my suggestion in comment #6?
If you don't reference a workbook fully, it will always look at the active workbook, which is not always what you want.

Hi RHCPgergo


I was just looking at that and think you may be right !!
Reasoning > when the initial Macro runs and creates the new WB (book1 / book2 )etc. then it takes a dive and Debugs ! however when I close the Debugger it automatically goes to the newly created WB not the initial tool with the Macro's within so I am assuming therefore that that WB is the active one when it dives??

However if I then select the original WB and run the macro's from Worksheetsshow it works perfectly

there pfore I assume Worksheet show is trying to unhide sheets where there are non hidden and then backit2 is trying to copy sheets that arent there....

PROBLEM!!!!

The Current WB or Tool is called "Week 34 possession creation tool" and have just been sent one with "WK43 Poss Tool" as its name ....

So there is no set name for the original work book !

So how do i get the start of WorkSheetShow macro to reference / run in the original WB??

Thanks

James

[james 35]

This is the end of the original sub which creates the new WB I think the adjustment to the code needs to be made here? to re focus the code back to the main WB once the new WB has been created ??

Please Login or Register  to view this content.

[RHCPgergo]

There is no code for creating a workbook here. It should be something like

Please Login or Register  to view this content.

Can't you post all the code that is in the module? Or post the whole workbook? It would be a lot easier to resolve any problems that way.

[james 35]

Hey RHCPgergo

Firstly really sorry for the late reply had to take my nipper to the hospital for CT scan on thursday and friday was day off and like an idiot I hadnt synced the file to my usb stick :-/

On a ligher note I have mangaged to resolve the program with the following Subs - they arnt perfect and would appreciate any advice you can offer with cleaning them up to run better and the Backit sub copies the sheets to all open workbooks :-/ not great but it works lol

many thanks and lioke I said any advice appreciated :-)

Please Login or Register  to view this content.

There is no code for creating a workbook here. It should be something like

Please Login or Register  to view this content.

Can't you post all the code that is in the module? Or post the whole workbook? It would be a lot easier to resolve any problems that way.

[RHCPgergo]

I would love to help with optimizing, but please post the workbook if you want me to help, cause it's kind of hard to derive what's needed from just code samples.

I hope your son gets better, I wish him well. (I had to look up "nipper" in a dictionary :D)

[james 35]

Hey RHCPgergo

Sorry for late response ! I'll see if I can post the workbook up tomorrow..
The nippers are on half term break this week so I've taken a few days annual leave..

My eldest was the one at the hospital - He's 8 and unfortunately it looks like more surgery for him... Next time be his 25th operation :-(
Long term problem since birth but he's a chirpy little dude :-)) Thanks fore the kind wishes :-))

And yes sorry its just our silly English slang lol

James

I would love to help with optimizing, but please post the workbook if you want me to help, cause it's kind of hard to derive what's needed from just code samples.

I hope your son gets better, I wish him well. (I had to look up "nipper" in a dictionary :D)