Macro to copy a row from one worksheet to another based on a value

[GAM055]

Hi!

I am clueless about macros and would deeply appreciate your help.

Is it possible to have a macro copy a row from one worksheet and paste it in a new row in another worksheet based on what value is in a cell of that row in a specific column?

Let me give you an example of what I'm trying to do:

A B C D
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Project[/TD]
[TD]Event/Task[/TD]
[TD]Program[/TD]
[/TR]
[TR]
[TD]12/17/2015[/TD]
[TD]Presentation at conference[/TD]
[TD]T[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]12/23/2015[/TD]
[TD]Holiday party[/TD]
[TD]E[/TD]
[TD]Teachers[/TD]
[/TR]
[TR]
[TD]1/14/2016[/TD]
[TD]Refresher class[/TD]
[TD]E[/TD]
[TD]Students[/TD]
[/TR]
[TR]
[TD]1/19/2016[/TD]
[TD]Training of volunteers[/TD]
[TD]T[/TD]
[TD]Volunteers[/TD]
[/TR]
[TR]
[TD]2/3/2016[/TD]
[TD]PowerPoint on new techniques[/TD]
[TD]T[/TD]
[TD]Other[/TD]
[/TR]
</tbody>[/TABLE]











I have multiple worksheets. The first worksheet, the one shown above, is the "overview". The other worksheets are titled according to the Program categories (I have about 6 or 7) - see column D.

So, I would very much appreciate a macro that would copy rows in the first worksheet and copy each one to other specific workbooks matching the value in column D. For example, again referring to the table above, after I enter 2/3/2016, "PowerPoint on new techniques", "T", "Other"... then Excel's macro would copy that row and paste it in the workbook called "Other". (I also have workbooks titled "Teachers", "Students", "Volunteers" and more).

Hope I am making my request clear!

IF you are able to help by showing me the macro, would you be so kind to change the color of the values that I can change (so that I don't mess up the rest?)

THANK YOU,

John

P.S. Ideally the macro would run automatically every few minutes or at least each time I open the document in Excel 2007.

 

[gdesreu]

Hi!

I am clueless about macros and would deeply appreciate your help.

Is it possible to have a macro copy a row from one worksheet and paste it in a new row in another worksheet based on what value is in a cell of that row in a specific column?

Let me give you an example of what I'm trying to do:

A B C D
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Project[/TD]
[TD]Event/Task[/TD]
[TD]Program[/TD]
[/TR]
[TR]
[TD]12/17/2015[/TD]
[TD]Presentation at conference[/TD]
[TD]T[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]12/23/2015[/TD]
[TD]Holiday party[/TD]
[TD]E[/TD]
[TD]Teachers[/TD]
[/TR]
[TR]
[TD]1/14/2016[/TD]
[TD]Refresher class[/TD]
[TD]E[/TD]
[TD]Students[/TD]
[/TR]
[TR]
[TD]1/19/2016[/TD]
[TD]Training of volunteers[/TD]
[TD]T[/TD]
[TD]Volunteers[/TD]
[/TR]
[TR]
[TD]2/3/2016[/TD]
[TD]PowerPoint on new techniques[/TD]
[TD]T[/TD]
[TD]Other[/TD]
[/TR]
</tbody>[/TABLE]











I have multiple worksheets. The first worksheet, the one shown above, is the "overview". The other worksheets are titled according to the Program categories (I have about 6 or 7) - see column D.

So, I would very much appreciate a macro that would copy rows in the first worksheet and copy each one to other specific workbooks matching the value in column D. For example, again referring to the table above, after I enter 2/3/2016, "PowerPoint on new techniques", "T", "Other"... then Excel's macro would copy that row and paste it in the workbook called "Other". (I also have workbooks titled "Teachers", "Students", "Volunteers" and more).

Hope I am making my request clear!

IF you are able to help by showing me the macro, would you be so kind to change the color of the values that I can change (so that I don't mess up the rest?)

THANK YOU,

John

P.S. Ideally the macro would run automatically every few minutes or at least each time I open the document in Excel 2007.

do you mean another workbook or worksheet? The title says one worksheet to another but workbook is then stated

 

[GAM055]

Hi Gdesreu,

Thank you for pointing out my confusing language!

This is what I meant:

Is it possible to have a macro copy a row from one worksheet and paste it in a new row in another worksheet based on what value is in a cell of that row in a specific column?

Let me give you an example of what I'm trying to do:

A B C D
[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Project[/TD]
[TD]Event/Task[/TD]
[TD]Program[/TD]
[/TR]
[TR]
[TD]12/17/2015[/TD]
[TD]Presentation at conference[/TD]
[TD]T[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]12/23/2015[/TD]
[TD]Holiday party[/TD]
[TD]E[/TD]
[TD]Teachers[/TD]
[/TR]
[TR]
[TD]1/14/2016[/TD]
[TD]Refresher class[/TD]
[TD]E[/TD]
[TD]Students[/TD]
[/TR]
[TR]
[TD]1/19/2016[/TD]
[TD]Training of volunteers[/TD]
[TD]T[/TD]
[TD]Volunteers[/TD]
[/TR]
[TR]
[TD]2/3/2016[/TD]
[TD]PowerPoint on new techniques[/TD]
[TD]T[/TD]
[TD]Other[/TD]
[/TR]
</tbody>[/TABLE]



I have multiple worksheets. The first worksheet, the one shown above, is the "overview". The other worksheets are titled according to the Program categories (I have about 6 or 7) - see column D.

So, I would very much appreciate a macro that would copy rows in the first worksheet and copy each one to other specific worksheets matching the value in column D. For example, again referring to the table above, after I enter 2/3/2016, "PowerPoint on new techniques", "T", "Other"... then Excel's macro would copy that row and paste it in the worksheet called "Other". (I also have worksheets titled "Teachers", "Students", "Volunteers" and more).

Hope I am making my request clear!

IF you are able to help by showing me the macro, would you be so kind to change the color of the values that I can change (so that I don't mess up the rest?)

THANK YOU,

John

P.S. Ideally the macro would run automatically every few minutes or at least each time I open the document in Excel 2007.

 

[My Aswer Is This]

I'm not knowledgeable on how to have a script run every few moments. And having it run on workbook open would then mean it can only run when you open the workbook. Why not just create a button on your sheet and when you click the button the script will run.
Let me know if that option is possible and if so I will write you a script. If not someone else here at Mr. Excel will need to help you.

 

[My Aswer Is This]

If your willing to run the script with a Button or a shortcut key or from the Macro Recorder Box you can use this script.
You need to have a sheet Named "Overview" which is the sheet this script will be run from.
The rows will be copied over to the sheet named in column "D"

Your comment:
IF you are able to help by showing me the macro, would you be so kind to change the color of the values that I can change (so that I don't mess up the rest?)
There is no part of the script you need to change.

Sub Copy_To_Sheet_In_Column_D()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets("Overview").Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To Lastrow
        Lastrowa = Sheets(Cells(i, 4).Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
        Rows(i).Copy Destination:=Sheets(Cells(i, 4).Value).Rows(Lastrowa)
    Next
Application.ScreenUpdating = True
End Sub

 

[GAM055]

Hi,

THANK YOU so much for your help. Unfortunately I encountered an error: "Run-time error '9': Subscript out of range".

I clicked on Debug and got the following line was highlighted:

Lastrowa = Sheets(Cells(i, 4).Value).Cells(Rows.Count, "A").End(xlUp).Row + 1

Please help me. Thank you!

 

[My Aswer Is This]

The script looks in column ("D") for a sheet name and copies the row to that sheet.
Are you sure all the values are spelled exactly the same way your sheets are named?
If the value in column "D" reads "George" then you need to have a sheet named "George"