Automatically transfer data between worksheets [Closed]

Posts
1
Registration date
Wednesday May 31, 2017
Status
Member
Last seen
May 31, 2017
- - Latest reply: vcoolio
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
- 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
See more 

2 replies

Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1537
0
Thank you
take a look at this:
http://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!
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
206
0
Thank you
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.