 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2007Copy row based on cell content
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 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. > Hi > [quoted text clipped - 13 lines] > Any assistance is appreciated. > Thanks Hi Jerry, Any chance of posting a link here to a sample file with your vba solution? It would benefit interested readers, eg myself ..  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- 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. > Hi Jerry, > > Any chance of posting a link here to a sample file with your vba solution? > It would benefit interested readers, eg myself .. Marvellous ! Many thanks for the sample. I'm going to enjoy learning how your sub gets it all done. Cheers. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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 [quoted text clipped - 7 lines] > sheet does not exist, it is created, so when you run the macro, several > sheets will be added to the workbook. 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? > Marvellous ! Many thanks for the sample. I'm going to enjoy learning how > your sub gets it all done. Cheers. [quoted text clipped - 9 lines] > > sheet does not exist, it is created, so when you run the macro, several > > sheets will be added to the workbook. Thanks for the kind offer, Jerry. Thought it was a very well annotated & instructive sub. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- >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 [quoted text clipped - 6 lines] > we can hijack the thread <g> - poor billinr hasn't been back since the > 15th? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.