Auto copy part of row in a worksheet to another worksheet based on criteria

**repke** · 12-15-2009, 03:46 PM

Hello, I'm a novice Excel 2007 user and appreciate all the help I can get. I have a workbook with monthly worksheets in it. When a certain data Type is selected from a drop down menu in that monthly worksheet than I would like to have it automatically enter specific data (Name, Date, Eval, Type) copied to another worksheet (CC) in the same workbook. I have been manually entering the data so far. Another thing, some of the data will be entered into the Monthly worksheets and some will only be manually entered into the CC worksheet so it would need to accomodate both methods of data entry. Please let me know if I need to clarify. I have attached the workbood, too. Thanks again so much for your help!

**JBeaucaire** · 12-15-2009, 04:52 PM

Right-click on the Excel logo to the left of the FILE menu and select VIEW CODE...paste in this workbook event macro:

Please Login or Register  to view this content.

Exit the Editor and save your workbook.

Now any changes on any of the monthly sheets in the "Offense" columns will transfer to the CCs sheet if they are CC offenses.

**repke** · 12-15-2009, 11:43 PM

Hi, I tried pasting the code into this workbook but it didn't work. I'm not sure what I'm doing wrong. Do I need to run the macro? I realize these are basic questions, my apologies. Also, how does the date get entered? Thanks again!!

**JBeaucaire** · 12-16-2009, 12:07 AM

Post up your amended workbook with the macro installed so I can see what's going on with your attempt. You do know you will have to save the workbook as a .xlsm and then reopen the workbook for it to work, right?

The Date is being taken from the section the *-CC code is entered. The macro looks at the top of each section in the "merged" cell and transfers what it finds there. It's working good in my testing.

**repke** · 01-03-2010, 07:12 PM

I copied and pasted the macro and have attached the file, I' new to macros so it is likely I missed a basic step. Any help would be greatly appreciated!

**repke** · 01-03-2010, 09:47 PM

I don't know what I did to get it to work. Thank you! I really appreciate it. Now I have another question. There are actually more worksheets in the real version of the workbook so how do I change the code to refer to these worksheets? Also, can code be added to modify/delete the item in the CCs worksheet when it is modified/deleted on one of the sheets it is pulled from?

Here are the actual sheets that contain the data that I'd like to have copied: Sheet1, Sheet10, Sheet12, Sheet14, Sheet 16, Sheet19, Sheet21, Sheet 23, Sheet25, Sheet4, Sheet6, Sheet8. The CCs sheet is actually Sheet29.

Thanks again!! I really appreciate all the help!

**JBeaucaire** · 01-04-2010, 02:49 AM

1) Remove the code from the CCs sheet. As indicated above, the macro provided goes in ThisWorkbook module only. I see it is in that module, too, and workgin properly.

2) Move the CCs worksheet so that it is the first sheet in the workbook, just like your example workbook. The macro is using the Sheet.Index values to decide if the data should be copied, so make sure the sheets occupy index positions 2-13. The sheet names are not used.

4) To have the macro refer to MORE sheets than those 12, put them all together from position 2 through "whatever" and then change the one line of code here to indicate the correct beginning and ending range of the sheet index positions:

Please Login or Register  to view this content.

4) Move your sheets listed in post #6 so they are AFTER the sheets above.

The macro should continue to work just fine.

**repke** · 01-04-2010, 09:12 AM

Thanks, but the difficulty I have is that for each month I two worksheets and they are placed one after the other for the year... Jan2010, Jan2010S, Feb2010, Feb2010S, etc. The Sheet names I listed in post 6 are from the code in the writer (not the name I have in the workbook). So I was hoping there was a way to refer to the Sheets listed in post 6 specificially regardless of the order. Also, is there a way to change the code so that it will modify/delete the information in the CCs sheet if it is modified/deleted in the other worksheets (e.g., Jan2010)?

Thanks again! Really appreciated!!

**repke** · 01-04-2010, 10:07 AM

I didn’t know what it meant to use a Sheet.Index value so I looked it up and from what I read it seems like a Sheets Code Name might be the best way to go with this workbook. How would I change the code to a Sheets Code Name? So would I change your line like this? Or do I use Sheet1.Select? Are there other lines that need to be changed? Also, is there a way to code it so that the data in the CCs sheet is automatically modified/deleted in the other sheets? Thanks for all your time and help!! I really appreciate it.

'Certain changes in every 4th column of sheets2-13 transfer to data to sheet1

'Certain changes in every 4th column of sheet1,sheet10,sheet12,sheet14,sheet16,sheet19,sheet21,sheet23,sheet25,sheet4,sheet6,sheet8 transfer to data to sheet29

I attached a document showing the editor names for the sheets, hopefully this will show it better than I'm explaining. Thanks again!

**JBeaucaire** · 01-04-2010, 12:25 PM

This should do it, list the sheet names in the highlighted section...this way it won't matter where they are in the workbook, as long as you list them by name accurately, they will trigger the transfer macro.

Please Login or Register  to view this content.

I have no ideas for allowing you to edit entries already transferred.

**repke** · 01-04-2010, 10:48 PM

Thanks!! It worked. I really appreciate it.

**JBeaucaire** · 01-04-2010, 11:40 PM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

If the PREFIX box is no longer available, just add [SOLVED] to the start of the title...

Auto copy part of row in a worksheet to another worksheet based on criteria

LinkBack

Thread Tools

Rate This Thread

Display

Auto copy part of row in a worksheet to another worksheet based on criteria

Re: Auto copy part of row in a worksheet to another worksheet based on criteria

Re: Auto copy part of row in a worksheet to another worksheet based on criteria

Re: Auto copy part of row in a worksheet to another worksheet based on criteria

Re: Auto copy part of row in a worksheet to another worksheet based on criteria

Re: Auto copy part of row in a worksheet to another worksheet based on criteria

Re: Auto copy part of row in a worksheet to another worksheet based on criteria

Re: Auto copy part of row in a worksheet to another worksheet based on criteria

Re: Auto copy part of row in a worksheet to another worksheet based on criteria

Re: Auto copy part of row in a worksheet to another worksheet based on criteria

Re: Auto copy part of row in a worksheet to another worksheet based on criteria

Re: Auto copy part of row in a worksheet to another worksheet based on criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions