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

[Thom78]

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

 

[vcoolio]

Hello Thom,

The following event code may help:-

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]

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]

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]

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

 

[vcoolio]

So it all works then?