Move certain data from one column to another sheet based on selection in drop down selection

Alexiz36 · Nov 4th 2021

Hello all,

I was introduced to this community as I was told it's the place to go for help on VBA.

I've got a project where after someone inputs data in columns A-H in sheet "Quotes" (Picture 1), the user would then select whether to open or close a job in Column I using a drop down I've already made. Upon selection, I'm trying to have the column move to either the "Closed Quotes" or "Open Jobs" based on the selection. When it pastes into "Closed Quotes", I'd like it to also mark Column J with the date that the selection was made (in the "Closed Quotes" sheet). For "Open Jobs", I'd the data to do the same with the date but also but delete the data from rows A-D and only paste rows E-I starting in Row C (so Row C to G [Picture 2]) rather than start in Row A, where I've struggled.

I know this is a lot but any help would be greatly appreciated.

Thank you so much

vcoolio · Nov 5th 2021

Hello Alexiz,

Could you please upload a sample of your file as images are not much help to anyone who may offer you assistance. We can easily test procedures with an actual file. Ensure that you use dummy data in your sample just as your images appear to have.

Cheerio,

vcoolio.

Alexiz36 · Nov 5th 2021

Good day vcoolio,

Here is a test file. There is some initial code in VBA when I was trying to figure this out.

Thank you very much.

**royUK** · Nov 5th 2021

You are making work for yourself and resorting to VBA unnecessarily.

Your data should be stored in one sheet with a column for Status. Then you wouldn't need to move data. You simply filter to look at the Status, you can even simplify this by using Slicers.

Data in Excel

Also consider converting the data to a Table then you wouldn't need all those unused formulas which is bad practice.

Overview of Excel Tables.

Spend some time learning to use Excel correctly before thinking about VBA, especially when you are resorting to Loops which are generally an inefficient way of doing this type of thing.

vcoolio · Nov 5th 2021

Hello Alexiz,

Should you want to stay with your current method, a worksheet_change event code should do the task for you:-

Code

Private Sub Worksheet_Change(ByVal Target As Range)


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


Application.ScreenUpdating = False


If Target.Value = "Close" Then
Target.Offset(, 1) = Date
Target.EntireRow.Copy Sheets("Closed Quotes").Range("A" & Rows.Count).End(3)(2)
ElseIf Target.Value = "Open" Then
Target.Offset(, 1) = Date
Range(Cells(Target.Row, "E"), Cells(Target.Row, "J")).Copy Sheets("Open Jobs").Range("C" & Rows.Count).End(3)(2)
End If


Application.ScreenUpdating = True


End Sub

Display More

With this code, each time you make an "Open" or "Close" selection from the drop downs, the data will be immediately transferred to its relevant sheet.

To implement this code:-

- Right click on the "Quotes" sheet tab.

- Select "View Code" from the menu that appears.

- In the big white code field that then appears, paste the above code.

You'll need to first delete all existing codes that you have.

I've attached your sample with the code implemented and all other codes removed. Test it to see if it's what you were hoping to achieve.

I hope that this helps.

Cheerio,

vcoolio.

Alexiz.xlsm

Alexiz36 · Nov 5th 2021

Thank you all very much for your help with this. I am able to now build myself a simpler than initially planned excel document to help with my organization.

Regards,

Alexi

vcoolio · Nov 5th 2021

You're welcome Alexi. I'm glad to have been able to assist.

Cheerio,

vcoolio.

**royUK** · Nov 6th 2021

As I said, VBA is unnecessary. Correct layout and the use of Excel's functions is by far the simplest way.

Move certain data from one column to another sheet based on selection in drop down selection

Participate now!

Share

Tags