Copy row based on cell content

[Guest]

Hi

I have a workbook with about 100 sheets; they are all formatted the same as
far as rows, columns, etc. The data in the cells is different.
I would like to copy a range of rows based on the contents of a cell on the
worksheet.
To further explain, I have one of three possible values in cell A1. Based on
this value, I want to copy the range of A5:M13 to another sheet, named the
same as the cell.
I need to repeat this process for all of the sheets in the workbook.

I would like to automate the process because the sheets are going to be
updated often, and I would need to revise this "summary" page after the
updates.

Any assistance is appreciated.
Thanks

 

[Guest]

I just did something very similar for another person in these pages - needed
to move data from a single sheet off to other sheets based on content of a
single cell, giving the receiving sheet the same name as was in the key value
cell. It was a VBA (macro code) solution. Should be relatively easy to to
modify to work with multiple sheets.

If you'd like to give that a try, attach a copy of the workbook to an email
and send it to HelpFrom @ jlathamsite.com (remove spaces) - all info in the
book treated confidentially. But it will help to both test and see your
current worksheet naming so that we can figure out when running the code
which are sheets we need to move data from and which are sheets we have moved
data to (and possibly added to the workbook along the way). The code I have
takes care of that also: if it finds a 'key value' of XYZ, for example, but
no sheet named XYZ exists, it creates it and then starts moving data.

 

[Max]

Hi Jerry,

Any chance of posting a link here to a sample file with your vba solution?
It would benefit interested readers, eg myself ..

---

 

[Guest]

Sure, I've used the original file with extra features (like formatting
borders on cells and hiding rows on some sheets) still in it. I've obscured
the data that was in it originally, so that should provide anonymity. You
can get the file here:
http://www.jlathamsite.com/uploads/MoveBasedOnColumnContents.xls

It moves data from the one sheet based on contents of column E. If a needed
sheet does not exist, it is created, so when you run the macro, several
sheets will be added to the workbook.

 

[Max]

Marvellous ! Many thanks for the sample. I'm going to enjoy learning how
your sub gets it all done. Cheers.

 

[Guest]

I think it's pretty straight-forward code, nothing terribly fancy in there
and hopefully my remarks to myself (and to the original recipient) will be
enough to let you figure out what it is doing. If not, feel free to ask
questions about it, I can be reached by email at (remove spaces):
HelpFrom @ jlathamsite.com

Or if you feel the answers would be beneficial to the folks here, I suppose
we can hijack the thread <g> - poor billinr hasn't been back since the 15th?

 

[Max]

Thanks for the kind offer, Jerry. Thought it was a very well annotated &
instructive sub.