Moving data to different sheets based on cell contents

[asml8d]

Hope I'm not being a pain, but I need help one more time... a manager at my POE is trying to help one of her team work smarter, not harder. On the attached spreadsheet, you can see she's got it set up so that when the employee enters data in the front page, depending on the contents of the drop down in column "C", the data in the row gets moved to the appropriate worksheet. She's accomplishing this through IF statements. The issue is that it moves the data laterally, instead of putting it in the first available row in the appropriate worksheet. Is it possible to get the data to move to the first available row when it copies from the main sheet to the secondary sheet; and if so, how?

You guys rock, btw... thanks so much for your help!


PVC REQUESTS COPY .xlsx

[mehmetcik]

Hi

This is easily done.

In your example the data is not moved. It is copied. It that what you want?

This can be done using an array formula but that is complicated and can use up a lot of memory and can be slow.

I would do this using about three macros. That would be fast and efficient.

Also what happens if you want to move the record again?


I have created something to get you started.

Have a play while I explain it.

My solution uses 3 macros.

The first is a workbook macro and runs whenever you change your selection in any sheet within the workbook.

Its main function is to stop you selecting or changing row 1 of any sheet.

It also stores the value if you select column C of "Kelly Tracking" this is so we can delete an existing entry, if you are re assigning an entry.

Please Login or Register  to view this content.

The second Macro is a sheet specific macro, all it does is check to see if you have changed a value in column C of "Kelly Tracking"
and kicks of the main macro. "MoveEntry"

Please Login or Register  to view this content.

We need MoveEntry because sheet specific macros cannot modify any sheet other than its own sheet.

Please Login or Register  to view this content.

This is just my first pass.

I wonder where you would enter your data?

Is the Data entered into Kelly Tracking? if so you may change column C at the wrong time and move half your data.

So maybe the data in column C may need to be moved to the last column.

Or maybe we should only run MoveEntry when all the cells in the row are filled.

I await your input.

[asml8d]

Sorry, I misspoke. I'd like the data copied, so that it remains in the first tab, and also is copied to whichever tab is specified by the drop down box. The data will remain in both tabs; I don't think there will be a need to change the dropdown box once it's selected.

[dflak]

Here's my entry into this contest. It makes extensive use of Excel Tables. The reason I used tables is because they expand to fit the data you have and copy formulas, etc. down automatically as you add rows. Also they are very "addressable." You work with column header names instead of column letters, so it's a lot more flexible and easier to write code and formulas for. To learn more about tables, see this article: http://www.utteraccess.com/wiki/inde...ables_in_Excel,

I made one change to your headers: I made the Borrower Name consistent across tables. It made part of the code easier to implement.

The heart of the coding is on the Configuration page. The table in Columns A:C I call "Table_Map" that tells the program where to put the information on the Kelly Page on whatever page it belongs. I didn't have enough information to map everything out, but you can do that and the code should work. There is a smaller table that maps the selected type to a page and table on the page.

To enter a record, type it in the row under the table. It will be come part of the table. Make sure you select a cell in the record you want to post, then click on the post button. There are checks to see if you selected a good cell, and a check to see if on the selected page, that you are not trying to make a duplicate entry.

[asml8d]

I apologize... things picked up at work and I haven't been able to try this out... I want to say thanks, though, for the help... I'll hopefully be able to try it this week and let you know how it goes.

[asml8d]

My apologies for not getting back on this... due to staffing changes here, this will no longer be needed. Thanks to those who replied!