Copy a range to different sheets depending on first cell contents

Hi all,

Mostly thanks to romperstomper, I have been using the below code to pull a range from a number of files and copy them all in to a new sheet. However, I now need to copy the range in to one of a number of sheets (3 at the moment) depending on the contents of the first cell.

Pretty sure I should be able to do this myself eventually, but I'm runnin short on time and so I could use your expert help again.

Many thanks

Matt

Please Login or Register  to view this content.

Hi pike,

Thanks for that, it gets me on my way. Would give me a sheet with all the ranges where the cell contents is "A". Next question, how do I expand this so I get further sheets with the ranges where the cell contents was "C" or "M"?

I tried setting rngOut to the first blank cell in the next sheet;

Please Login or Register  to view this content.

And then launching an essentially identical sub that ifs for the next value i want, but it doesn't like that above code, tells me rngOut is "empty".

What incredibly dense mistake am I making here?

Or, even better, can I get it to read all the ranges and then divide them in to separate sheets for every value that is in that cell?

Sorry, what I need to do overall is to pluck this particular range from all these workbooks and then divide them up between the sheets of a new workbook according to the contents of a particular cell.

Attached is an example of the sheets I am pulling teh data from, with the important cell highlighted in red.

Hope this helps.

Cheers

Matt

Bumped. A bit rude of me but I could really use a solution in the short term, doesn't need to be pretty...

Cheers

Hi pike,

Thanks for that, I think I am now very nearly there - for a short-term solution anyway.

I am currently using the below code, gets pretty ugly towards the end I know, but it is nearly doing the job;

Please Login or Register  to view this content.

Problem is, I am getting an "automation error" first runthrough of the loop when it tries to set rngDest if the target worksheet is completely blank. If I manually stick something in column A it is fine but it would be nice to avoid that.

Cheers

Matt

Sorry, I think I explained that wrong. The problem occurs when the sheet I am copying the range to is completely blank.

Hi, tried that and it has got rid of my automation error, but now each range that goes in to a particular sheet is overwriting the previous one. Any ideas?

Cheers

Matt

Tried that, unfortunately the second copy to a sheet is still overwriting the first

My current full code is below, and I've attached all my test workbooks in case there is something in there I should of mentioned but haven't.

Many thanks for all the help thus far.

Cheers

Matt

Please Login or Register  to view this content.

Quick question - is there a reason why icol is defined from Sheets("C") rather than wksDest? Will this still work if there is more or fewer columns of data in Sheet C than the other sheets?

Longer problem - second time it gets to case A, it still overwrites the data we got first time round.

Unfortunately yes, it seems to be pasting them both in to column B.

Yes, did I say row somewhere? That's me being sloppy.. Personally i think it's an admirable trait in a programmer...

It's very pretty (you should see the amount of utter rubbish that is commented out of my macro now), but it has the same problem.

The good news is I worked out why.

The bad news is it's painfully silly and obvious and if I'd spotted it before now possibly pretty much everything you guys have come up with since Thursday would have worked...

Please Login or Register  to view this content.

So thanks for your time and... erm... sorry...

Incidentally, is there a way of doing this where I don't have to tell it what the possible contents for that cell are and the code will create sheets for every different thing that is in there?