VBA to move data

[wavery]

Hello,
I an in need of a VBA Code to move data from one tab to another based on the action column.

Example: This is the main sheet (Tab) holding all data
Sheet Name: FUTURE
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]ACTION[/TD]
[TD="align: center"]Slot Number[/TD]
[TD]Slot Mast ID[/TD]
[TD]Poller Name[/TD]
[TD]Address[/TD]
[TD]Area[/TD]
[TD]Section[/TD]
[TD]Location[/TD]
[TD="align: center"]Terminal Controller Name[/TD]
[TD]TC[/TD]
[TD]Line[/TD]
[TD]Spot[/TD]
[TD]Amount[/TD]
[TD]Description[/TD]
[TD="align: center"]Type ID[/TD]
[TD]Accounting[/TD]
[TD]Program[/TD]
[TD]System[/TD]
[TD="align: center"]J Program[/TD]
[TD]Boot[/TD]
[/TR]
[TR]
[TD]Remove[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Move[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Install[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

---

Below are the action sheet's (Tab's) below:
Sheet Name: REMOVE, MOVE, INSTALL

REMOVE SHEET (TAB)[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Slot Number[/TD]
[TD]Area[/TD]
[TD]Section[/TD]
[TD]Location[/TD]
[TD]Terminal Controller Name[/TD]
[TD]TC[/TD]
[TD]Line[/TD]
[TD]Spot[/TD]
[TD]Amount[/TD]
[TD]Description[/TD]
[/TR]
</tbody>[/TABLE]




MOVE SHEET (TAB)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Slot Number[/TD]
[TD]Area[/TD]
[TD]Section[/TD]
[TD]Location[/TD]
[TD]Terminal Controller Name[/TD]
[TD]TC[/TD]
[TD]Line[/TD]
[TD]Spot[/TD]
[TD]Amount[/TD]
[TD]Description[/TD]
[/TR]
</tbody>[/TABLE]




INSTALL SHEET (TAB)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Slot Number[/TD]
[TD]Area[/TD]
[TD]Section[/TD]
[TD]Location[/TD]
[TD]Terminal Controller Name[/TD]
[TD]TC[/TD]
[TD]Line[/TD]
[TD]Spot[/TD]
[TD]Amount[/TD]
[TD]Description[/TD]
[/TR]
</tbody>[/TABLE]

---

In the example I need to move the data to the correct sheet (tab) based on the action in column A on the FUTURE sheet (tab). The Slot Number is the key so to speak as all data is based on the Slot Number. The action names are always the same however they are not always in the same order as the example. The action drop down list is a "Data Validation list" stored in the workbook.

Please let me know if you need more info.

Thank you,
Wade

 

[alansidman]

Why not just filter on the Slot Number Field. Then you will only have the particular data.

On the data tab, Highlight your field names row and select filter. In the slot number field, click on the drop down and select the item to filter by unchecking the other boxes.

 

[wavery]

I need the data on the related tabs for project submission.

 

[vcoolio]

Hello Wade,

A Worksheet_Change event code may do the task for you. Place the following code into the "Future" sheet module:-

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim x As Long: x = Target.Row
        
If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

        Union(Range("B" & x), Range("F" & x & ":" & "N" & x)).Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)
        Sheets(Target.Value).Columns.AutoFit
 
End Sub

From your opening post, I'm assuming:-

- You have your headings in Row1 with data starting in Row2.
- You only want data from Columns B and Columns F:N from the "Future" sheet to be transferred to the relevant destination sheet.

With this code, you'll need to ensure that the selection from the drop downs in Column A are your very last entries. Hence, each time that you make a selection from the drop down values, the relevant row of data will be automatically transferred to the relevant source sheet.

Please test the code in a copy of your actual workbook first.

To implement this code:-

- Right click on the Future sheet tab.
- From the menu that appears, select "View Code".
- In the big white code field that then appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.

 

[alansidman]

Filter as indicated and then copy and paste.