VBA to move data

wavery

New Member
Joined
Jun 29, 2018
Messages
25
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
ABCDEFGHIJKLMNOPQRST
ACTIONSlot NumberSlot Mast IDPoller NameAddressAreaSectionLocationTerminal Controller NameTCLineSpotAmountDescriptionType IDAccountingProgramSystemJ ProgramBoot
Remove
Move
Install

<tbody>
</tbody>

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

REMOVE SHEET (TAB)
Slot NumberAreaSectionLocationTerminal Controller NameTCLineSpotAmountDescription

<tbody>
</tbody>




MOVE SHEET (TAB)
Slot NumberAreaSectionLocationTerminal Controller NameTCLineSpotAmountDescription

<tbody>
</tbody>




INSTALL SHEET (TAB)
Slot NumberAreaSectionLocationTerminal Controller NameTCLineSpotAmountDescription

<tbody>
</tbody>





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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
 
Upvote 0
Hello Wade,

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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top