Copying rows on excel automatically to other tabs

[dean.grimshaw]

Hi there apologies if this is simple stuff but im still a novice...

I have an excel spreadsheet, with 8 tabs,, Long Term is the main tab where i 'enter information'

the other tabs 1,2,3,4,5,Dead and Filled are Locked tabs.

I enter my information in each row on the Long Term tab. i need the spreadsheet to automatically move the rows to the relevant tab. I have set up a validation with a drop down for 1-2-3-4-5-Dead and Filled in one coloumn. is this possible and if so please tell me how.

so if a line is - John Smith , 25/11/2012, 28/5/13, then Dead.. It would need to go to the 'Dead TAB'...

and if it was Guinness, 29/11/2012, 21/12/12 then 2 ... it would need to go to the '2 Tab'

HOWEVER the lines would still need to stay in the Long Term Tab to be edited


Thanks in advance

Dean

[arlu1201]

This can be accomplished. I have a few questions -

1. By locked, do you mean they are password protected too? If yes, you will need to provide the password in the code (you can add it in later) so that the code unprotects the sheet, copies the data and protects it again.

2. Which is the column that will contain the dropdown showing which tab the data needs to go to?

3. Once you have run the macro and copied the data to the relevant tabs, if you make any changes to the Long Term tab, and want to run the macro again, the data will be duplicated in the destination tabs. Hence, will it be fine if the macro clears all the data from the destination tabs and then loads fresh data from the top to the last row of the Long Term Tab?

[dean.grimshaw]

Hi There in anser to your question, By locked i do mean password protected, and in regards to the colomn that is named 'Grade'...
and yes thats fine if it deletes and re runs the macro to cpy the data again.

Just a further question would it be possible to also make it so when in the 'Grade' colomn you select Dead they become Red and filled they become green?

That would be great,.

[arlu1201]

What is the column address (A or B or C) for Grade?

You can do the color changing through conditional formatting. You do not need to include it in the macro.

[dean.grimshaw]

I can do the conditional formatting thanks...

Grade is in Column H.

[arlu1201]

Try this code -

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

[dean.grimshaw]

great stuff thanks