Transfer Data IF....

Closed
MT - Nov 22, 2015 at 09:00 AM
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Nov 23, 2015 at 05:03 AM
Hi,

I am in the process of building a database for injury management (I am a Sport Scientist). I have a master sheet which is basically a monthly sheet with headings:
A2- Treatment date, B2- Athlete, C2- By who, D2:G2- injury, H2:J2- treatment, K2:N2- prognosis, O2:R2- comments.

This is the monthly 'run' sheet and I also have individual sheets for each athlete to be able to easily track an injury history. What I would like to be able to do is have information automtically transferred from the master 'run' sheet to each individual athletes history profile. Is there a way I can do this and filter by Athlete name, so that all data doesn't get sent to one place, but gets sent to the correct place? Help is much appreciated.

MT
Related:

2 responses

vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Nov 23, 2015 at 03:57 AM
Hello MT,

Perhaps a BeforeDoubleClick event code will do the job for you as follows:-


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Application.ScreenUpdating = False

If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)

Sheets(Target.Value).Select
Sheets(Target.Value).Columns.AutoFit
Target.EntireRow.Delete

Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub


Just double click on any Athlete's name in Column B and the relevant row of data will be transferred to that Athlete's work sheet.

Following is a link to my test work book for you to peruse:-

https://www.dropbox.com/s/ddeuv3a4klorigf/MT.xlsm?dl=0

Just double click on any name in Column B to see it work.

The code also deletes the "used" data from the Master sheet once it is transferred to the individual sheet.

This code needs to go into the sheet module so to implement the code, right click on the Master sheet tab and then select "view code". In the big white field that appears, paste the above code. Go back to the Master sheet and double click away!

Let us know if this works for you.

Cheerio,
vcoolio.
0
Thanks vcoolio, this is great. How do I take this one step further so that the original data doesn't get deleted from the master so that this essentially becomes a continuous running sheet over time. Again, thanks in advance.

EDIT: I'm working Mac if that makes any difference.
0
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Nov 23, 2015 at 05:03 AM
Hello MT,

Just remove line no. 11 from the code. The Master sheet is going to become extremely large!

I don't have any experience with Macs but I believe that Excel still operates as per a normal PC.

Cheerio,
vcoolio.
0