Transferring information from previous spreadsheet [Solved/Closed]

Report
Posts
3
Registration date
Saturday February 28, 2015
Status
Member
Last seen
March 1, 2015
-
skreechy
Posts
7
Registration date
Tuesday March 31, 2015
Status
Member
Last seen
April 7, 2015
-
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

Posts
7
Registration date
Tuesday March 31, 2015
Status
Member
Last seen
April 7, 2015

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?
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
210
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.
Posts
7
Registration date
Tuesday March 31, 2015
Status
Member
Last seen
April 7, 2015

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
skreechy
Posts
7
Registration date
Tuesday March 31, 2015
Status
Member
Last seen
April 7, 2015

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
210
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.
skreechy
Posts
7
Registration date
Tuesday March 31, 2015
Status
Member
Last seen
April 7, 2015

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
vcoolio
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
210 > skreechy
Posts
7
Registration date
Tuesday March 31, 2015
Status
Member
Last seen
April 7, 2015

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.
vcoolio
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
210 > vcoolio
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020

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.
skreechy
Posts
7
Registration date
Tuesday March 31, 2015
Status
Member
Last seen
April 7, 2015
> vcoolio
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020

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
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
210
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.
skreechy
Posts
7
Registration date
Tuesday March 31, 2015
Status
Member
Last seen
April 7, 2015

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
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
210
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.
skreechy
Posts
7
Registration date
Tuesday March 31, 2015
Status
Member
Last seen
April 7, 2015

ok, thanks,

I'll keep working on it
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
210
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.
JoDWil
Posts
3
Registration date
Saturday February 28, 2015
Status
Member
Last seen
March 1, 2015

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!
vcoolio
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
210 > JoDWil
Posts
3
Registration date
Saturday February 28, 2015
Status
Member
Last seen
March 1, 2015

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.
JoDWil
Posts
3
Registration date
Saturday February 28, 2015
Status
Member
Last seen
March 1, 2015
> vcoolio
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020

Will do! Thanks again