Transferring information from previous spreadsheet

Solved/Closed
JoDWil Posts 3 Registration date Saturday February 28, 2015 Status Member Last seen March 1, 2015 - Feb 28, 2015 at 08:06 PM
skreechy Posts 7 Registration date Tuesday March 31, 2015 Status Member Last seen April 7, 2015 - Apr 7, 2015 at 04:32 AM
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!

7 replies

skreechy Posts 7 Registration date Tuesday March 31, 2015 Status Member Last seen April 7, 2015
Mar 31, 2015 at 12:05 PM
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?
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Mar 31, 2015 at 09:54 PM
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.
0
skreechy Posts 7 Registration date Tuesday March 31, 2015 Status Member Last seen April 7, 2015
Apr 1, 2015 at 02:15 PM
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
0
skreechy Posts 7 Registration date Tuesday March 31, 2015 Status Member Last seen April 7, 2015
Apr 1, 2015 at 02:20 PM
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Apr 2, 2015 at 01:16 AM
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.
0
skreechy Posts 7 Registration date Tuesday March 31, 2015 Status Member Last seen April 7, 2015
Apr 2, 2015 at 09:57 AM
Hey,

Hope your memory has come back :)

Thanks a lot for this,
It almost does it, but it doesn't do it in the background (automatically)
so I have to run the script each time, also can I stop have far down the page excel looks for data by specifying for example B40 or does it have to go to the end?

So close.

Thanks
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252 > skreechy Posts 7 Registration date Tuesday March 31, 2015 Status Member Last seen April 7, 2015
Apr 2, 2015 at 08:42 PM
Hello Screechy,
I have a terrible headache!

The following code should do the job for you:-

Private Sub Worksheet_Change(ByVal Target As Range)

   If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
   If Target.Cells.Count > 1 Then Exit Sub
   If Target.Value = vbNullString Then Exit Sub
   
Application.EnableEvents = False
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("B100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
   End If
Next
   
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.CutCopyMode = False
MsgBox "Data transfer completed!", vbExclamation

End Sub


It is a Worksheet_Change event and will trigger as soon as you enter a value in Column B and click away. As I said in Post #6, it could become quite annoying so enter all your other values per row first and leave Column B 'til last. My personal choice would be to add a button, assign the macro (the first code above) to the button, do whatever data entry is necessary and then click on the button to transfer all at once.

also can I stop have far down the page excel looks for data by specifying for example B40 or does it have to go to the end? 


I'm not sure why you would want to do this other than perhaps you have a totals row or other data further down the page which would interfere with the row search. The norm is to search from the bottom up and happens very quickly but, if you look at the above code, the search starts at row 100 [Range("B100").End(xlUp).Offset(1, 0)].

Or, do you mean you just want to transfer a fixed range of data each time (A7:B40)?

Cheerio,
vcoolio.
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252 > vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022
Apr 2, 2015 at 08:51 PM
Sorry Skreechy,
I forgot to mention to you how to implement the above code,

- On the Sheet1 tab, right click and select "View Code".
- In the big white field that appears, paste the above code.
- Make sure that you save it.
Done!

BTW, did you want to delete the data from Sheet 1 once it is transferred to Sheet 2? it would be a good idea otherwise the data will continue to duplicate in Sheet 2.

Cheerio,
vcoolio.
0
skreechy Posts 7 Registration date Tuesday March 31, 2015 Status Member Last seen April 7, 2015 > vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022
Apr 4, 2015 at 04:58 AM
Hi Vcoolio,

Thanks for the 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.

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 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)

I'd like the data to remain in sheet1

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,

Thanks for the teaching along the way btw, it's very informative and helpful, the way you describe things helps me to understand the codes a little more (with my limited knowledge)

Thanks again
0

Didn't find the answer you are looking for?

Ask a question
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Apr 4, 2015 at 06:57 AM
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.
0
skreechy Posts 7 Registration date Tuesday March 31, 2015 Status Member Last seen April 7, 2015
Apr 6, 2015 at 01:32 AM
Hi vcoolio,

Thanks again,

I now see what you mean with removing the data once copied, i'm getting multiple copies on sheet 2,
But, because the data on sheet1 may need updating, it has to stay,
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)
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 coulmn on sheet1 one has been returned to a 0 value.

I hope this explains it a little better?
I've attached a sample worksheet so you can see the example a bit clearer,

https://www.dropbox.com/s/ekiciimwzrmbaar/TEST%20TEMPLATE%20C-W%20INVOICE.xlsx?dl=0

Thanks
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Apr 6, 2015 at 04:04 AM
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.
0
skreechy Posts 7 Registration date Tuesday March 31, 2015 Status Member Last seen April 7, 2015
Apr 7, 2015 at 04:32 AM
ok, thanks,

I'll keep working on it
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Mar 1, 2015 at 04:37 AM
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.
-1
JoDWil Posts 3 Registration date Saturday February 28, 2015 Status Member Last seen March 1, 2015
Mar 1, 2015 at 05:23 AM
Haha not hiding, more like locked away a Sydney hospital...

And thank you so much for taking the time to do this! The spreadsheet works wonderfully, and is enormously helpful! Looking forward to using this tomorrow!

Thanks again, and have a great week!
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252 > JoDWil Posts 3 Registration date Saturday February 28, 2015 Status Member Last seen March 1, 2015
Mar 1, 2015 at 05:38 AM
Hi Doctor,

Try the code in a copy of your actual work book first, just in case.

Let us know how it goes.

Glad that I could help.

Cheerio,
vcoolio.
0
JoDWil Posts 3 Registration date Saturday February 28, 2015 Status Member Last seen March 1, 2015 > vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022
Mar 1, 2015 at 05:56 AM
Will do! Thanks again
0