Trying to tighten up some code

Hi All,

I am very new to the vba world and learning on the job...I completed a macro which does (almost) everything I want to do...but it is inelegant to say the least...can someone please help me tighten it up? As you can see it is doing the exact same thing...from sheet TS, looks for 2011, copies those rows to sheet2011, keeps going and then looks for 2012 and copies those rows to sheet 2012 and so on. I tried some for next loops and couldn't get them to work.

I then repeat this process for another 8 sheets, so you can see how the code grew in length. The only consideration for application for the next sheets is they must be processed in a specific order ie. TS, TS1, TS2 TS3 etc., Help here also greatly appreciated, but tightening up the first part will help so much!

Thank you!

Steve

Please Login or Register  to view this content.

Try this untested code
[EDIT]
This only works for one sheet, I missed the second part of the question.

Please Login or Register  to view this content.

The main point is not to use Selection, it is rarely required and only slows your code
You can also omit Cstr in most cases

Hope this helps

Hmm? On second thoughts this might be a bit cleaner

Please Login or Register  to view this content.

Hope this helps

DrSteve
welcome to the forum or maybe..

Please Login or Register  to view this content.

Marcol likes Select but this will work also

Please Login or Register  to view this content.

Thank you for all your help guys...I see the principles of what you have suggested

I tried removing all the references to different sheets to paste from to simplify things. But I couldn't make any of the suggestions work.

In Marcol's first suggestion it worked for all sheets apart from 2011!
In MarvinP's suggestion it seemed to copy data into sheets 2012 onwards, but not 2011!
It looks like there is a very tiny thing not set right.

In Pike's suggestion it complained myarray not being defined. So I took out the for/next loop and had it try on just the TS sheet..

Please Login or Register  to view this content.

These suggestions are all so close...I feel it! I think that's why I stopped programming back in my old "micro" days!

Thanks guys.

Steve

You need to have a sheet named "2011" and "2012". If you have spaces before or after it may not work. Check the exact spelling of your sheet tabs.

If not the spelling of the Tabs, then perhaps you have spaces in the data. "2011" is not equal to "2011 " as there is a space after the second string.

Try this modified code.

Please Login or Register  to view this content.

Provided your sheets are named TS, TS1,TS2 to TS whatever and the number suffixes are continuous then this should work.

use with marcol example

Please Login or Register  to view this content.

steve the whole story would have been nice

Please Login or Register  to view this content.

or ?

Please Login or Register  to view this content.

The code in post #8 will copy data from all sheets where the sheet name begins with TS
Did you try the sample workbook? It copies sheet TS

The extra loop was added to cover

.....they must be processed in a specific order ie. TS, TS1, TS2 TS3 etc.,

This loop counts an indefinite number of sheets begining with TS and processes them in order regardless of their position in the workbook

However what you asked for wasn't what you wanted...

Go with Pikes' last code.

[EDIT]
@ snb Your code doesn't run, what am I missing?

Debugging without the datasheet is not possible.

'subscript out of range' means that no such sheet exists:
whether "TS", "ECFQA", "MTCD", "PD", "OQ", "OE", "WCFQA"
or cl.text (i.e the values in column F form F2 downwards)

Better attach the workbook