Automatically transfer data between worksheets

Closed
Murphs21 Posts 1 Registration date Wednesday May 31, 2017 Status Member Last seen May 31, 2017 - May 31, 2017 at 10:57 AM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Jun 2, 2017 at 10:35 PM
Hii

I'm currently trying to make my work life a little easier and have created an excel spreadsheet of all the incoming paperwork we have for students. If possible I want to be able to transfer a row of data onto the appropriate worksheet.

For example
my current format is
Forname|Surname|Course |Status etc etc
Testing | MISTER|Plumbing| A |

When the status for the learner is changed to 'F' or 'W' i would like the row to move to the appropriate worksheet. Is this possible? if so please let me know, it would make my work life so much more easier every time i seem cut data and paste it the worksheet doesn't filter properly afterwards

Kind Regards
Karine

2 replies

Blocked Profile
May 31, 2017 at 04:16 PM
take a look at this:
https://ccm.net/faq/53497-how-to-manipulate-data-in-excel-using-vba

All of what you have asked can be "cut and pasted" from that article, you just have to put it all into the right order based on the final product you want!

Let us know!
1
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Jun 2, 2017 at 10:35 PM
Hello Karine,

Further to ac3mark's post, another option would be to use a Worksheet_Change event code, such as follows:-


Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

If Intersect(Target, Columns(4)) Is Nothing Then Exit Sub

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

Application.EnableEvents = True
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


If your "Status" column is Column D (or column 4) then, with the above code, once you enter the appropriate status, the entire row of data will be transferred to its individual sheet once you click away (or press enter or down arrow). The code will also clear the "used" data from the main (input) sheet.

I'm assuming that each status has an individual sheet named the same as the status.

To implement the code, right click on the main (input) sheet. From the menu that appears, select "view code" and in the big white field that then appears, paste the above code.

Remember to make the entry in the "Status" column the very last data entry.

I hope that this helps.

Cheerio,
vcoolio.
0