Macro not working on all sheets

[tazz]

Hello all,
I have a workbook with 2 worksheets and I tried to apply the same macro separate on both of them. Macro is working is working only in one of them(TEST) and on the other one(PIECE) I get a message of error.
Also there is another problem: the sheet that is working has a very huge size(out of 497 kb this sheet has 496 kb) and I can't use this sheet as a template.
What I want is to fix the size of the first sheet(something is wrong) or to make the other one working.
I tried everything I know but it was not enough.
Thank you for your help.
Please find the file in the attachment.

 

[NARAYANK991]

Hi ,

The macro uses several named ranges which refer to the sheet tab named TEST ; if you try running the same macro on another sheet , you will need to make changes to the named ranges , and the code itself.

Narayan

 

[tazz]

Hi Narayan,
Could you please help me in making this changes to make the lisp working on any sheet. Also do you have an idea why the sheet has such a big size?
Thanks
Ps: I forgot to specify that the mastermind of this code is Sirjb(thanks)

 

[SirJB7]

Hi, tazz!

That VBA code looks familiar to me

But it seems that many changes happened from its original container file. So if I were you I'd try doing this:
a) rename worksheet PIECE to anything
b) copy worksheet TEST as PIECE (this will duplicate all named ranges with worksheet scope)
c) copy actual data from old PIECE worksheet to PIECE
d) activate/select worksheet TEST and run the macro
e) activate/select worksheet PIECE and run the macro

Then come back and tell us if you succeeded; I hope you'll do.

Regards!

 

[SirJB7]

Hi, tazz!
We overstepped into each other.
I actually don't remember anything about this code not its original source, so it'd help if you post here the original link and then avoid repeating the explanation of what did it intended do to.
I didn't tested your uploaded workbook, just gave a 1st look.
Regards!

 

[tazz]

Hi SirJB,
This is the link for the original source of the code http://chandoo.org/forum/threads/selecting-and-putting-data-in-columns.14069/#post-84492
Thanks