Help needed on saving then clearing content except selected rowsClosed

Question

Hi 
I am writing a spreadsheet for my ward for TCI list which has admissions, discharges and transfers.

I want to be able to save then clear the contents of the sheets except overdue patients (that can be selected) moving them to the top of the spreadsheet. I am guessing this would be made easier using a macro but i have very little knowledge on them, any help would be much appreciated
Thanks

vcoolio · Answer

Hello JPuk,

The description of what you are trying to do is not overly clear. Could you please supply a sample of your work book (please use dummy data) showing inputs and the expected results. Are you wanting to save "used" data to a separate sheet? What is a TCI list?

You can upload a sample of your work book by using a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back here.

Someone should then be able to help you.

Cheerio,
vcoolio.

JPuk · Answer

Many thanks for getting back to me so quickly the link below will take you to workbook.

https://www.dropbox.com/s/5ypo06mz119ph8x/TCIlistbasic.xltm?dl=0

vcoolio · Answer

Hello JPuk,

It is still very confusing as to what you are trying to do. Could you at least answer these two questions from my first post.:-

1) Are you wanting to save "used" data to a separate sheet?
2) What is a TCI list?

In relation to question (1) above:-

I'm assuming that the Admissions sheet is your main input sheet. Correct?

When you are done with a patient, they are either discharged or transferred to another ward. Correct?

If the above is correct, do you need their details transferred to the relevant sheet (Discharge or Transfer)? If so, exactly which details?

Time Now & Arrival TimeThe time now is when the bed manager contacts us to tell us of patients tocome into the wardThe user will press these buttons and it will input the time to the right of the button, but still allowing the user to enter the time if they so wish. Below is the code i am using. Each button will have to have it own timestamp code (unless anyone can come up with another idea).

With the Time Now and Arrival Time, I would suggest that you not use so many buttons as this will create a mass of unnecessary code which will use a mass of memory resource. One Double Click event should take care of these two time columns for you.

Please advise your thoughts.

Cheerio,
vcoolio.

JPuk · Answer

Sorry for confusing you.

TCI means "To Come In" basically another way of saying people who will be admitted. We mostly get admissions from the emergency dept, but occasionally we will have people transferred from other wards to us because of the level of care we can provide.

Yes the admissions sheet is the main data source

"Are you wanting to save "used" data to a separate sheet?"

i thought the best way would be to save the whole workbook under that days date and time of saving it. I may be wrong but if i do this then i will not be able to make any searches to bring up past patient transfer/discharge data. The more i think about it moving the used data to another sheet keeping them in date of admission order would probably be the best thing.

"When you are done with a patient, they are either discharged or transferred to another ward. Correct?" 

Yes this is correct however they may also be transferred to another hospital.

The only details that would need to be transferred are the name and bed number. The other headings would not be needed on the transfer and discharge sheets .

You one double click idea sounds good, beyond my knowledge though.

I might add my ward would be the only ward using this in the hospital as A&E has its own bed board system and other wards use a pen and diary system since they do not have the number of admissions and discharges we do.

Would it make things easier if i had userforms for Admissions, Ward Transfers, Hospital transfers and discharges. if so then i will need a crash course on making them.

Thank you for your time and effort in replying and helping.

vcoolio · Answer

Hello JPuk,

Thanks for the clarifications.

My recommendations would be:-

1) Forget about the individual buttons for the Time Now and Arrival Time columns and use the double click event (I'll show you what to do shortly).

2) Create separate sheets for Transfers and Discharges.

3) Once patients are discharged or transferred, archive their full data in a separate sheet (and call it "Archive" I suppose) and delete their data from the Admissions sheet. Deleting "used" data will bring data of current patients to the top of the Admissions list. You will be then able to access the full history of archived patients (sounds a bit impersonal!) if needed through an archive search which we can create.

4) Personally, I haven't got much time for userforms as data still needs to be placed in the relevant input sheet regardless. I just consider userforms as an unnecessary step requiring more code. However, if you still like the idea of userforms, have a look at Trevor Easton's site: 

https://www.onlinepclearning.com/

Trevor is extremely good at this sort of thing.

5) In relation to the clock that you have in the Admissions sheet, I would ditch the code (even though it it very neat and compact) and simply place the following formula in cell C2:-

=Today()

The date will then perpetually update and appear in this cell.
Then in cell C3, place the following formula:=

=Now()

then format the cell for time only. The time will then  perpetually update and appear in this cell.

However, should you prefer to keep the clock code, then assign the code to a Workbook_Open event. The time and date will then update each time the work book is opened.

Now, for the double click event code (test this first in the dummy copy of your work book):-

Right click on the Admissions tab and from the menu that appears, select "view code". In the big white field that appears, paste the following code:-


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim lr As Long
      Dim cell As Range

lr = Range("B" & Rows.Count).End(xlUp).Row

For Each cell In Range("B6:B" & lr, "G6:G" & lr)
      If ActiveCell.Column = 2 Or ActiveCell.Column = 7 Then
      ActiveCell.Value = Format(Time, "hh:mm")
      End If
Next

End Sub


Every time that you double click on a cell in Column B (from B6 down) or a cell in Column G (from G6 down), the time will appear in that cell.

Let us know your thoughts.

Cheerio,
vcoolio.

JPuk · Answer

Thanks for the help will try your suggestions out tonight.

I do like the double click idea and the archive, the simplified version of the clock is very neat and tidy and if i can do it without using code so much the better.

I will let you know how i get on

Thanks again for your help

vcoolio · Answer

Hello JPuk,

No worries.

In the meantime have a look at my test work book at the following link:-

https://www.dropbox.com/s/3dko5dgdn3u65k5/JPuk%20%282%29.xlsm?dl=0

I think it is close to what you were thinking of doing. Click on the Archive button to see it all work.

You'll notice that I have squeezed out Columns A & G. These are the columns in which you had the little clock pictures and since my test work book is a copy of the sample you supplied, I simply got them out of the way (I know, lazy!). However, when all is done and dusted, I would recommend that you start a new work book and use Columns A & G. After all, they are there to be used. You'll just need to modify the cell references in the code. The codes are in Modules 2, 3 & 4. I won't post them here just yet. I'll do that after you post back with your thoughts.

I think that there may still be an issue to resolve with the Discharge and Transfer sheets but, again, I'll wait for your thoughts.

Cheerio,
vcoolio.

Help needed on saving then clearing content except selected rows

7 responses

Subscribe To Our Newsletter!