Transfer Data IF.... Closed

Question

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

vcoolio · Answer

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.

vcoolio · Answer

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.

Transfer Data IF....

2 responses

Similar discussions

Subscribe To Our Newsletter!