Copy row from one sheet to another based on one cell value

Thom78

New Member
Joined
Mar 30, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Hi,

I’m a new to the group and I spent a number of hours trying to get VBA for below.

you will need to speak to me like a child lol. This is all new to me.

hopefully I have done this correctly, I am looking to copy and delete rows from main dashboard tab into two other sheets, depending on 1) “On Hold” and “completed” this would have say 1 in each worksheet.

The column “M” is where the options are to have the two outcomes.

the main dashboard tab is called “In Flight Updates
1) worksheet tab is called “On Hold”
2) worksheet tab is called “Completed”

if anyone can help, that would be amazing

thanks
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
Hello Thom,

The following event code may help:-

VBA Code:
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns(13)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

Application.ScreenUpdating = False

Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete

Application.ScreenUpdating = True

End Sub

Once you enter the required criteria in any cell in Column M and click away, the code will transfer the relevant row of data to the relevant destination sheet. The code will then delete the row of data from the source sheet ("In Flight Updates").

I'm assuming that the data in the "In Flight Updates" sheet starts in Row2 with headings in Row1.

To implement the code:-
- Right click on the In Flight Updates sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

If you create a drop down list with the two criteria in each cell in Column M, the code will immediately execute on selection of a criteria.

I hope that this helps.

Cheerio,
vcoolio.
 

Thom78

New Member
Joined
Mar 30, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Hi Vcoolio,

Thanks for your response and code.

I didn't have the Select "View Code option on my spreadsheet on the tab
I clicked on Developer
I then clicked on Visual Basic
I then clicked on "Insert"
Then selected module
I then copy and pasted your code.

I then selected the column M like you said but nothing happened, sorry? I'm not sure what I am doing wrong.

Thanks so much for the assistance.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
Hello Tom,

An event code, such as that in post #2, needs to be placed in the sheet module not a standard module as you have done.
Try again as per the instructions in post #2. The options have to be there once you click on the sheet tab.

Cheerio,
vcoolio.
 

Thom78

New Member
Joined
Mar 30, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Hello Tom,

An event code, such as that in post #2, needs to be placed in the sheet module not a standard module as you have done.
Try again as per the instructions in post #2. The options have to be there once you click on the sheet tab.

Cheerio,
vcoolio.
ah sorry I must be still sleepy, managed it, thank you.

Thanks so much Vcoolio
 

Watch MrExcel Video

Forum statistics

Threads
1,129,483
Messages
5,636,603
Members
416,928
Latest member
ezmeng

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top