Copy 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
0
billinr (4)
2/15/2007 2:42:16 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
317 Views

Similar Articles

[PageSpeed] 44

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.

"billinr" wrote:

> 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
0
Utf
2/16/2007 1:47:03 AM
Hi Jerry,

Any chance of posting a link here to a sample file with your vba solution? 
It would benefit interested readers, eg myself ..
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---  


0
demechanik (4694)
2/20/2007 4:59:19 PM
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" wrote:

> Hi Jerry,
> 
> Any chance of posting a link here to a sample file with your vba solution? 
> It would benefit interested readers, eg myself ..
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---  
> 
> 
> 
0
Utf
2/20/2007 11:44:00 PM
Marvellous ! Many thanks for the sample. I'm going to enjoy learning how 
your sub gets it all done. Cheers.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"JLatham" <HelpFrom @ Jlathamsite.com.(removethis)> wrote in message 
news:24260912-735E-421A-9D00-1FA8C181851A@microsoft.com...
> 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. 


0
demechanik (4694)
2/21/2007 12:11:38 AM
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" wrote:

> Marvellous ! Many thanks for the sample. I'm going to enjoy learning how 
> your sub gets it all done. Cheers.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> --- 
> "JLatham" <HelpFrom @ Jlathamsite.com.(removethis)> wrote in message 
> news:24260912-735E-421A-9D00-1FA8C181851A@microsoft.com...
> > 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. 
> 
> 
> 
0
Utf
2/21/2007 12:28:49 AM
Thanks for the kind offer, Jerry. Thought it was a very well annotated & 
instructive sub.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JLatham" <HelpFrom @ Jlathamsite.com.(removethis)> wrote in message 
news:E6733471-0391-480C-A06F-A6555405082D@microsoft.com...
>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?


0
demechanik (4694)
2/21/2007 1:49:45 PM
Reply:

Similar Artilces:

Row limits with Publisher
Is it possible to exceed publisher table limit of 128 rows? I have a spreadsheet that I want to import with almost 2,500 rows. Thank you in advance for your assistance! Regards, Chuck cbouline wrote: > Is it possible to exceed publisher table limit of 128 rows? I have a > spreadsheet that I want to import with almost 2,500 rows. Thank you in > advance for your assistance! Given that Publisher can't flow tables across pages, that's going to have to be a really long page! (I don't have an answer for your question.) -- Ed Bennett - MVP Microsoft Publisher http:...

how do i cancel user restricted values in a cell
Data>Validation>Clear All? Gord Dibben MS Excel MVP On Thu, 14 Aug 2008 08:58:07 -0700, grahamcotton <grahamcotton@discussions.microsoft.com> wrote: ...

Row Inserts
I notice that in Excel 2003 you can protect a sheet but have an option that would continue to allow rows to be inserted. Is there a way to do this in Excel 2000? When I protect the sheet then row inserts are no longer allowed. Thanks for any help. Mike B -- Disclaimer: Any comments made are personal and do not reflect the thoughts or policies of this company. Hi Mike, You can insert the rows with a macro, as long as the macro turns off protection. You do not need a password for protection but if you do, then the macro would have to use that password. For macros to insert rows see ...

Finding a string in Excel cells
Not sure if this is an Excel problem or a general Microsoft products "feature", but here goes:- I've inherited a HUGE spreadsheet with 6 figures of cells in total(!) and I'm told that some have been highlighted for query or error with "???" in the cell (in addition to other text in that cell). Trouble is, as soon as I do "find" with the "?" character, it seems to ignore it, as if ? is some reserved character. Does anyone have any idea how I can get round this? Ta! The ? character is used as a wild card character (any single character). ...

Change scroll rows from 1 to 3 with mouse wheel
Does anyone know how to set the mouse scroll amount from 3 rows to 1 row in Excel 2007? There used to be an option in 2003, but I can't find one in 2007. It is set in Mouse in the Windows Control Panel needhelp wrote: > Does anyone know how to set the mouse scroll amount from 3 rows to 1 row in > Excel 2007? There used to be an option in 2003, but I can't find one in 2007. ...

Row formatting based on cell content #2
That didn't turn out the way i had hoped to explain what i get in m files....but if you can imagine a space in the first colum where heading appears BLUETOOTH DBT120 D-LINK Bluetooth USB Adapter DCF650BT D-LINK Bluetooth PDA Compact Flash CABLE CABLE - SURGE FILTER SF001NP SURGE FILTER PC POWER CABLE CABLE - IDE HDD/CD ROM AUDCAB AUDIO CABLE - BLACK TO BLACK IDECAB IDE DATA CABLE (HDD/CD ROM) - 3 drop IDECAB133 IDE DATA CABLE -ULTRA ATA 133 (HDD) CABLE - NETWORK CABUTP005 0.5 METRE UTP RJ45 LEVEL 5 NETWORK CABLE CABUTP01 1 ...

cascade copy?
Is there such a thing as a cascade copy? I know there's cascade delete where if you delete the main record, all related subrecords are deleted but what if I want to do the reverse. For example I have a quote that has subpackages related to it. If I want to copy the main quote, I want all related subrecords copied along. Or is this a programming thing? Thanks, Alan "Alan" <Alan@discussions.microsoft.com> wrote in message news:7A7551D8-B482-4911-9452-3FE801FED2D4@microsoft.com... > Is there such a thing as a cascade copy? I know there's cascade delete where ...

Insert / remove a row from protected sheet excel 2003 #2
Hi, I have my worksheet protected. I set the frist 5 rows all cells to Locked. The rest of the cells are Unlocked. When applying protection I ticked the boxes saying allow insert of row and allow delete rows. Yet when the protection is applied the icons / menu options for inserting / deleting rows are grayed out. What am I doing wrong? Thanks! Michiel. ...

what is the attribute name for this : "automatically update e-mail addresses based on email-address policy"
There is a check box above for the exchange 2007 console. What is the equivalent of the attribute name ? "EmailAddressPolicyEnabled" -- Tim. "IT STAFF" <jkklim@hotmail.com> wrote in message news:4E9A4E62-2028-435A-B982-0059BAA273F8@microsoft.com... > There is a check box above for the exchange 2007 console. > > What is the equivalent of the attribute name ? ...

Place a form control value in Access to a specific cell in Excel.
I didn't get a response on my last post, so I'm hoping someone will have an idea on this request. Below is my original post: Hi, All! I'm looking for some help in moving data from my Access form to specific cells in an Excel template. I'd like to create a command button on my Access form that will open the .xlt file and transfer some of the values on my form and plug them into the .xlt. For example, I have the cost of equipment calculated on my form control (3 printers @ $300 each = $900), the command button will capture that value and plug it into the capital expenditure ...

Top row of excel stays even in sorting
I need a header in the top row of my excel worksheet so that even when I sort the data the top row remains the same. 2003:- Data>>Sort>>My Data Range As>> Check the “Header Row” Option Button. 2007:- Press Alt+D+S the Sort Dialog Box will appear Press Alt+H which will select the “My Data has headers” check box. -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "willyd" wrote: > I need a header in the top row of my excel worksheet so that even when I sort > the data the top ro...

cell comment
how can i copy the cell comments from one workbook to another. i have no trouble coping comments to a different part of same workbook. thanks Try: Copy > Paste special > Check "Comments" > OK (Just tried it. It works within the same book, and to another book as well) -- Rgds Max xl 97 ---------------------------------- Use xdemechanik <at>yahoo<dot>com for email ----------------------------------------- "bob" <bjlstrong@shaw.ca> wrote in message news:HppRb.298227$X%5.296318@pd7tw2no... > how can i copy the cell comments from one workbook ...

Hidden Recipients show up in query-based distribution group
I have several recipients that I have hidden from the GAL because they have left the company, but they still show up in the Preview tab of our query-based distribution groups. Does anyone have a solution to this problem?? Thanks, Dave modify the ldap query to exclude recipients with msExchHideFromAddressLists attribute (set to true)... ? -- Bharat Suneja MCSE, MCT -------------------------------- "DaveF" <DaveF@discussions.microsoft.com> wrote in message news:7E57B631-E4F4-4B39-99F0-C1F4FA784CC0@microsoft.com... >I have several recipients that I have hidden from th...

Create separate chart for each row in excel
I am attempting to create a separate chart for each row of my worksheet (about 300 rows = 300 separate charts). Is there an easy way to do this? The actual data range will change month-to-month, so I would not want to have to recreate 300 new charts each month individually. While I have never created (or tried to create) 300 charts in a single workbook, it is easy to believe that performance and stability of Excel might become a question. Do you have to have 300 charts? Or will one chart (or a few) that can be easily changed to reflect new data suffice? If so, adapt the ideas behind ...

Outlook 2000
Hi there, Using Win XP Pro with Office 2000 Pro SR-1 (all Office and Win XP updates applied). Having major problems with Outlook 2000 locking up. When I am unable to get into Outlook I then go into Ctrl+Alt-Del and look at the processes. Here I can find up to SEVEN versions of Outlook.Exe running, as well as a few WinWord.Exe (use HTML and Word as editor). Meanwhile Outlook will not open until I remove all these processes from the list. Then it opens after a 1-minute hard drive delay. Often Outlook will be running but not visible (tied up in the background I think). If I were ...

00/01/1900 In the date cell when linked cell is empty?
Hi I have 52 worksheets all linked up to a total sheet that is working very well thanks to RD All I need to do now is sort out the date this is copied from sheet1 onto the totals page and works fine when the date is put into sheet1. However if the cell is blank on sheet2 the date is put into the totals sheet as 00/01/1900 when the date is put in sheet2 all is well with the right date entered into the totals sheet. So how do I get the totals date to stay blank until the weekly sheets have a date put into the sheet please. the format I am using is below many thanks for all the help I ...

Combining cell with the & function and keep the individual cell fo
Is there a way to keep the original format of the cell when combining the cells together? For instance, my formula is =c4&" "e4. I would like c4 to be bold, but not e4. Is this possible? Hi this is not possible with formulas -- Regards Frank Kabel Frankfurt, Germany "Kelly Ma" <Kelly Ma@discussions.microsoft.com> schrieb im Newsbeitrag news:9C2F054D-96BD-426E-AD2F-E433DFB8EA3F@microsoft.com... > Is there a way to keep the original format of the cell when combining the > cells together? For instance, my formula is =c4&" "e4. I would li...

View all linked cells?
I was wondering if anyone knew of a way to display a list of all link in a given workbook? Similar to looking at the source code to a website. Thank you for you time -- Message posted from http://www.ExcelForum.com Hi maybe not exactly what you're looking for but see http://www.bmsltd.ie/DLCount/DLCount.asp?file=FindLink.zip -- Regards Frank Kabel Frankfurt, Germany > I was wondering if anyone knew of a way to display a list of all links > in a given workbook? > > Similar to looking at the source code to a website. > > Thank you for you time! > > > --- &g...

Go to Blank Cell Macro Problem
Hi I am trying to get a macro to go to the first blank cell in a specific column i.e. column A has data from row A1 to A15. I'd want to run a macro that will select cell A16. I found this code below which does this on a 'Normal' sheet. The problem i have is that the window is split and frozen at cell A1 (to retain headings and the Button for the macro). This seems to be preventing the code from running correctly. Can anyone suggest an alteration to the code, or a different approach? .....Sub findfirstempty() Set Rng = Range("a2") If Not IsEmpty(Rng.Value) Then If IsE...

How do I calculate a weighted average and exclude the empthy cells
I am looking to complete my formula for calculated weighted averages. Here is what I have: A B C D 1 weight 20% 30% 50% Avg results 2 results 1 100% 100% 90% 95% 3 results 2 100% 90% 47% The formula I have thus far is for D2: =average(a2*$a$1)+(b2*$b$1)+(c2*$c$1) which works, however, when I apply the same formula to row 3, it creates an incorrect average as it calculates the empty cell. Can someone please help me resolve my delemna and provide me with possible solution? Thank you! 1. H...

Changing a cell color to reflect positive or negative total
How do I add a formula to make a cell color (red for negative, green for positive) reflect a positive or negative total when adding/subtracting 2 different cells. i.e. cell A43-A49, total sits in cell E49. If total is negative, then cell E49 turns red; if positive, cell A49 turns green. -A43 is a total of 40 previous cells added while A49 is the total of 4 previous cells added. Thanks. Alex Use conditional formatting. See http://www.contextures.com/xlCondFormat01.html -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alex Vega&quo...

Hyperlink button to a specific cell in excel
Hi, I've set up a database made up of 60 + files and god knows how many worksheets using hyperlinks; however, I would like use buttons instead of hyperlinks in some cases because it looks more professional and indicative of what one would expect a database to look like. Anyways, I need the links to be able to jump to a specific cell in a specific worksheet in a specific workbook just like I'm able to do using hyperlinks. Help Pl JayWes, If, for example, a hyperlink was in cell C6, the following macro placed in the worksheet module would "run" the hyperlink. You ca...

XL2003: Pulldowns usu. to populate 1 cell, can we have entries for 3 cells?
I was wondering about something. This would revolutionize things considerably; I have a spreadsheet that I created 5 years ago that I use repeatedly. The difficulty is that if I could expand its efficiency, then it would be much more useful and would cut down on my work tremendously. When I enter items into the hidden worksheet that provides the pulldown info, it is usu. data that populates 1 cell in the column of the target sheet that is pulling the data. 1:1, in other words. Is there a way to have several pieces of information that populate several different cells in the target sheet? ...

macro to copy and edit then delete a worksheet
I have created a macro that will open the workbook, print preview, copy a worksheet. After I create the copy I want to edit the contents then print a range. Upon exiting I want to delete the copy I created save and close the workbook. I have been able to do everything except do the editing in the worksheet before the macro prints, deletes and closes. Any suggestions Are you copying the worksheet to a new workbook? If yes, then you can do all your editing, print it and just close that workbook. Since that workbook hasn't been saved, you don't need to delete it. Kind of... ...

How can I create a rule to copy OUTGOING mail to a specific folde.
I want to have e-mails that I send to various recipients automatically routed to folders containing all my correspondence with them. So far, when I try to create such a rule, it only ends up applying to INCOMING mail, not outgoing. What am I missing? You first. What version of Outlook? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, ckanis asked: | I want to have e-mails that I send to va...