Transferring information from previous spreadsheetSolved/Closed

Question

Hi everyone, 

I'm a junior doctor from Australia, and I'm trying to use excel to make my ward management a more efficient process! Everyday we get new patients on the ward, but most patients are the same from the day before. For the patients that are the same, I want to be able to transfer over the patients background and issues list from the previous days spreadsheet, as these would not change. 

Heres a screenshot of what I'm going for... The first two columns are all worked out, and are easily updated by copying the hospital's online patient list into a template. The history and issues however are not part of the hospital's online database, (as I'm the one who figures that out once the patient is admitted and unfortunately we still use a paper system to record notes).



As the bed numbers change day to day, and I have up to 40 patients, its just too hard to go through the list and copy paste! Instead is there a way for me to make excel link a patients name/ patient number/ DOB in a current spreadsheet, with the background and issues from the day before's spreadsheet, and let auto transfer the right patients history/issues to the new spreadsheet?

Would greatly appreciate any suggestions! 
Thank you!

vcoolio · Answer

Hello JoDwil,

Whereabouts in this Great Southern Land are you hiding?

Your screenshot is difficult to read but I think that the following code may do the job for you:-

Sub CopyData()
Application.ScreenUpdating = False
  Dim lRow As Long
  Dim PatientID As String
Sheets("For the Ward").Select
lRow = Range("A" & Rows.Count).End(xlUp).Row

PatientID = InputBox("Please enter the required Patient ID.", "Patient Details.")
  For Each cell In Range("B2:B" & lRow)
    If cell = PatientID Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "H")).Copy Sheets("Patient Info.").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "H")).Delete Shift:=xlUp
  End If
Next cell
Sheets("Patient Info.").Columns.AutoFit
Sheets("Patient Info.").Select
End Sub

You can have a look at my test work book here:-

https://www.dropbox.com/s/mpmmemmlpr65ur2/JoDWil.xlsm?dl=0

to see how it works. Your actual work book will no doubt have alot of text in it but just format your cells to accommodate this (but please don't use merged cells!). The "Patient Info." sheet should autofit to accommodate the text from the "For the Ward" sheet.

You may note that I have added a Patient ID in Column B. This is to simplify the transfer code and will uniquely identify your patients. I just made up the ID numbers so you can do as you wish here. The code will have a Patient ID input box pop up when you click on the Transfer Data button. Enter the required Patient ID and the details of that patient will be transferred from the "For the Ward" sheet to the "Patient Info." sheet.
This method will allow you to randomly select patient details to transfer as I doubt that there will be any real order in a day of patient care! The code will also delete the patient details from the "For the Ward" sheet as you would probably not want to clutter up this sheet with "used" data.

I hope that this helps.

Regards,
vcoolio.

skreechy · Answer

HI,

Great info here, I'm relatively new to Excel, but i know it does what I want, it's just getting to that point

Is there a way to change this so it automatically moves the data over to the 2nd sheet (patient info) based on there being a value in the patient info field?
I'm trying to set up a spreadsheet very similar to this one with slightly different titles, but the logic is the same.
Also is it possible for the data going across to Sheet2 to start from line 10 rather than line 2?

vcoolio · Answer

Hello Skreechy,

Is there a way to change this so it automatically moves the data over to the 2nd sheet (patient info) based on there being a value in the patient info field?

Yes, there is. However, this would need to be a Worksheet_Change event and could become quite annoying after a while. If you look closely at JoDWil's screenshot, you'll notice that in the "Identifying Info" column, on each row, part of the required information is a Patient Number. So, to keep things simple, the Patient Number (or ID) may as well have its own column for the code to identify with as you can see in  the test work book:-

https://www.dropbox.com/s/mpmmemmlpr65ur2/JoDWil.xlsm?dl=0 

Also is it possible for the data going across to Sheet2 to start from line 10 rather than line 2? 

Yes, it is possible. But if your Headers/Titles are in rows 1 - 9, then the code will work as is for you and will start populating from row 10.
Let me know if this is not the case.

I hope this helps.

Cheerio,
vcoolio.

skreechy · Answer

Hi,
Thanks for replying, I'll try to explain it a bit better,
I'm trying to move a row (horizontal) from sheet1 to sheet2 automatically based on there being content in column b (vertical), I think I had mixed the names up before.
From what I see of this spreadsheet its very similar, but you have to specify a patient id to get the data to move, id like the data to move over if column b has any data in it, then if it has data in in, then to transfer that row to sheet2, and when it goes to sheet2, for it to start populating sheet2 from row 21 (for example)

I'll attach a screen shot if I can, that will make it easier to see



Thanks

vcoolio · Answer

Hello Skreechy,

Try the following code in a standard module:-

Sub Transfer()

Application.ScreenUpdating = False

Dim lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("B7:B" & lRow)
   If cell.Value > 0 Then
   Range(Cells(cell.Row, "A"), Cells(cell.Row, "P")).Copy
   Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
   End If
Next
   
Application.ScreenUpdating = True
Application.CutCopymode=False
Sheets("Sheet2").Select

End Sub

I'm just experiencing some computer memory overload problems (possibly me as well!) at the moment so I have not been able to test it for you but please post back and let us know how the code works so far.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Skreechy,

Further to your above reply:-

Yes, you're correct, there are notes and info from line 40 down so the data transfer will be between a specific range of rows as you said, (A7:A40) from sheet1 to transfer to sheet2 from row 26 and below, The data does transfer from row 26 down, but I'm trying to get my head around the code, it's not so easy to decipher. 

That's OK. Seeing that you are transferring each row individually as a task is completed, the range gives you plenty of room to move but I still recommend that you clear the data from sheet 1 once it is transferred because if you don't, you will run out of room based on the fact that your dataset is restricted to A7:A40. Clearing the "used" data leaves a clean sheet for continuous input, especially in a restricted range and, what's more, you will still have a record of the same data in sheet 2 (and I am assuming that you back up on a daily basis).
Not clearing the data from sheet 1 will result in continuous duplicates in sheet 2.

Sheet 2 is obviously a "clean sheet', so you can replace this line of code:-

 Sheets("Sheet2").Range("B100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

with this 

 Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

To explain this line, the code searches sheet 2 from the bottom up looking for the next available row to place the latest transferred data and, with a "clean sheet", this happens quickly and uninterrupted. The PasteSpecial xlPasteValues bit is a method of transferring the cell values only (no formatting, formulae etc.).

The idea is to create a sheet that automatically populates with the data from sheet1 to sheet2 to eliminate the copy and pasting of data every time a report is filled as only the  data that has a value in it from sheet1 column B is required as its a completed task. (I hope I'm making sense) 

This is what the code does: transfers a row of data based on a value being placed in Column B. No buttons required, no manual running of the code. As mentioned earlier, it is a Worksheet_Change event (which is why the code is placed "in the sheet" rather than a standard module) which means, basically, something will happen on the active sheet every time something else happens (an event). In this case, that event is entering a value in a cell in a row in Column B then moving to the next cell by clicking away or using the arrow keys on your keyboard thus triggering the transfer of data. Basically, Column B is an "active" column waiting for your input to trigger the transfer of data, so, as you fill in data row by row, it is best to leave the Column B input 'til last.

If you're interested, there is another way of having Column B as an active column, transferring data by individual rows at a time, but it will involve a button.


I'd like the data to remain in sheet1. 

Not a good idea with a restricted range. Refer to my first answer above.


Also does the data transfer complete box have to pop up every time data is entered into column B? That has to be ok'd before I can continue each time. Also, if I make a mistake with data entry, ie, I enter 5 into B7, then realise it should be 0, the data doesn't reset on sheet 2 when a 0 value is re entered, 

The message box is not necessary. To remove the message box, delete this line of code from the macro:-

MsgBox "Data transfer completed!", vbExclamation

As with anything like this, you will need to check that your inputs are correct prior to transferring. If you have transferred data with an incorrect entry, the corrected entry will not overwrite the previous in sheet 2. You will have an additional row of data in sheet 2 so you will need to delete the incorrect entry from sheet 2.

Or, are you saying that a row of data with an actual zero (0) value in Column B is not being transferred to sheet 2?

I hope that all this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Skreechy,

Add this line of code to the macro, just after "Next":-

Sheets("Sheet2").Range("B21:Q" & Rows.Count).RemoveDuplicates Columns:=1, Header:=xlYes

This should take care of the duplication in sheet 2.

what i'm trying to do is create a 2nd sheet (invoice in this exercise) where the data that has been given a numerical amount (B) is moved over (but the whole row cells B-Q)

Each whole row of data is transferred with this code once a value is placed in the corresponding cell in Column B, row by row.

this is where it fails for me, so the code you've given is great, but if the user makes an error the 2nd sheet keeps all entries, even if the original column on sheet1 one has been returned to a 0 value. 

I reiterate my previous reply:-

As with anything like this, you will need to check that your inputs are correct prior to transferring. If you have transferred data with an incorrect entry, the corrected entry will not overwrite the previous in sheet 2. You will have an additional row of data in sheet 2 so you will need to delete the incorrect entry from sheet 2. 

Also, you will need to unmerge any merged cells in your formatting and reformat without them. Merged cells create havoc with coding.

Cheerio,
vcoolio.

Transferring information from previous spreadsheet

7 responses

Similar discussions

Subscribe To Our Newsletter!