Original macro altered to work only on Prefixed WB no longer working?? Advive please ??

[james 35]

Hi guys

Here is the original macro that works to add sheets to all open WB -

Please Login or Register  to view this content.

Ive tried to ammend it to work only on WorkBoos with the Prefix BRM in the name...

as below

Please Login or Register  to view this content.

However it bugs out and simply stops at the underlined and I'm struggling to figure out why??

Can anyone advise??

Many Thanks

James

[Norie]

Perhaps.

Please Login or Register  to view this content.

[AndyLitch]

Try replacing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[james 35]

Perhaps.

Please Login or Register  to view this content.

No joy I'm affraid Norie, have I forgot anything basic here??

James

[james 35]

Try replacing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Tried that MR litch and still debugging on the same line of code :-(

James

Please Login or Register  to view this content.

[JosephP]

perhaps

Please Login or Register  to view this content.

[james 35]

Thanks Joseph!

I'm no longer at my desk so will give it a try tomorrow and get back to you with the result !!

Many thanks

James

perhaps

Please Login or Register  to view this content.

[AndyLitch]

Slacker

[james 35]

Andy I have been called worse lol !!!

Sorry not got back to you guys sooner just been really busy... But Joseph that worked a treat thank you !!! And thanks Andy also !!

If I could be cheeky is there a way of getting that working macro to delete the first 4 sheets from each Wb before adding the additional sheets its designed to ??

Or should I start another thread for that??

Thanks again guys

[JosephP]

will the target workbooks have more than 4 sheets to start with?

[JosephP]

if they will

Please Login or Register  to view this content.

[james 35]

will the target workbooks have more than 4 sheets to start with?

Yes Joseph they have about 20ish sheets... from sheets 5 onwards they are generic for an access point..

Sheets one to 4 contain the relevant data that alters each time they are utilised...

hence its only ever the first 4 sheets affected pal..

James

[james 35]

if they will

Please Login or Register  to view this content.

Aha that works well joseph however I have just noticed that some people have been saving the packs with just the generic data ie the first 4 sheets missing lol could i Specify the sheets by name ie

Please Login or Register  to view this content.

So as to delete the specific sheets if they exist and if not just add the newer ones ??

Thanks

James

[JosephP]

Please Login or Register  to view this content.

as long as either all or none of the sheets exist. if only some might exist you'll have to loop through the array

Please Login or Register  to view this content.

[james 35]

Please Login or Register  to view this content.

as long as either all or none of the sheets exist. if only some might exist you'll have to loop through the array

Please Login or Register  to view this content.

Brilliant Joseph Thankyou!!!

If I name them "Cover Sheet*" etc. would that delete Say Cover Sheet and also Cover Sheet 1 If some Numpty had duplicated them? I would I have to incorperate the Name Like option as with the BRM pack prefix?

Thanks

James

[JosephP]

you'd have to use "like" again

[james 35]

you'd have to use "like" again

Like this?

Please Login or Register  to view this content.

[JosephP]

more like this

Please Login or Register  to view this content.

[james 35]

Damn it just started to bug out on the Line below Error message is Runtime 1004 copy method of worksheet class failed ??

Any Idea's ?

Please Login or Register  to view this content.

[james 35]

I shut excel completely again and its working fine again now though the named like coding above doesnt work it debugs at the underlined??

Please Login or Register  to view this content.

Thanks

James

[JosephP]

what's the error?

[james 35]

runtime error 2147221080 (800401a8)
automation error Joseph :-/

lol nightmare !!

james :-)

[JosephP]

oh-I'm a dumbass

Please Login or Register  to view this content.

[james 35]

oh-I'm a dumbass

Please Login or Register  to view this content.

LOL

Ill have to test it tomoz now as im just about to leave

But many thanks for all the help :-)

James

[james 35]

Hey joseph P

Finally managed to test it and i'm getting the Error "End if without Block if"???

Not a clue lol

James

Please Login or Register  to view this content.

[JosephP]

you seem to have deleted a bunch of code lines

Please Login or Register  to view this content.

[james 35]

I honestly dont see it lol

you seem to have deleted a bunch of code lines

Please Login or Register  to view this content.

[JosephP]

what you posted was missing the

Please Login or Register  to view this content.

lines

[james 35]

How the hell I managed to do that is beyond me lol I must still be hungover !!!

Thanks For all the help Joseph ! works a treat

what you posted was missing the

Please Login or Register  to view this content.

lines

[JosephP]

you're welcome
hope the day gets better ;-)

[james 35]

you and me both !!!