Sort date daily

Hewlett-packard Pavilion a4316f-b deskto...
April 17, 2010 at 13:17:30
Specs: Windows Vista
I have a big problem with this daily report since I have to do it manually every single day. Is there a way to automated this in macro or vlookup . Help

Name Room Status From Date
Wayne,G 440-2 Medicare AAA 1/23/10
Chin, P 520-3 Private BBB 2/23/10
King, L 422-4 Insurance CCC 1/23/10
Cohen, A 545-3 Medicare AAA 2/23/10

1. This data base keep adding up daily so data range will not work.
2. Need to print report on daily admissions. So all patients admit for 1/23/10 will be print out of 1/23/10

Date Name Status
1/23/10 King, L. Insurance
1/23/10 Wayne, G. Medicare

See More: Sort date daily

April 17, 2010 at 15:07:58

Here is a macro that should do what you want.

Your source data is in a worksheet named "Source"
Your report is created in a worksheet named "Report"
If these are not the names used, either change the names of the worksheets or change them in these lines in the macro:

'change name of source worksheet here
strSrcWS = "Source"
'change name of report worksheet here
strRptWS = "Report"

The source data is in these columns, starting at row 2

	A		B	C		D	E
1	Name		Room	Status		From	Date
2	Wayne, Greg	440-2	Medicare	AAA	1/23/10
3	Chin, Peter	520-3	Private		BBB	2/23/10
4	King, Larry	422-4	Insurance	CCC	1/23/10
5	Cohen, Alex	545-3	Medicare	AAA	2/23/10

Due to the way that the macro finds the end of the source data, all cells in column A below the last data must be empty.

On the report page the date will be entered in cell C1 as follows:

	A	B		C
1	Report	for		1/23/10
2	Date	Name		Status
Add the other headings, as shown.

When the macro is run, this is the result:

	A	B		C
1	Report	for		1/23/10
2	Date	Name		Status
3	1/23/10	Wayne, Greg	Medicare
4	1/23/10	King, Larry	Insurance

The macro is triggered by either changing the date in cell C1 on the report page, or to re-run for the same date, select C2, click in the formula bar and click the adjacent check mark.

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

To load the macro, do this:
In the worksheet named "Report", right-click on the name tab at the bottom of the worksheet.
Select View code
In the visual basic window that opens, enter this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHnd:

'stop changes made by this macro from re-triggering it
Application.EnableEvents = False

If Target.Address = "$C$1" Then
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim rngDestStart As Range
Dim intDestOffst As Integer
Dim strSrcWS As String
Dim strRptWS As String

'change name of source worksheet here
strSrcWS = "Source"
'change name of report worksheet here
strRptWS = "Report"

'set start of source data range
Set rngStart = Worksheets(strSrcWS).Range("A2")
'find end of source data
Set rngEnd = Worksheets(strSrcWS).Range("A" & CStr(Application.Rows.Count)) _
'set start of destination data
Set rngDestStart = Worksheets(strRptWS).Range("A3")
'set destination offset counter
intDestOffst = 0

'loop through all dates in column E
'and find those equal to requested date in cell C1
For Each rngCell In Worksheets(strSrcWS).Range(rngStart, rngEnd)
    'date is in column E - offset =4 from column A
    If rngCell.Offset(0, 4).Text = Target.Text Then
        'dates match - so put data into report table
        'source data is in cols. E, A & C (source offsets 4, 0 & 2)
        'put date in column A - offset 0 from column A
        rngCell.Offset(0, 4).Copy _
            Destination:=rngDestStart.Offset(intDestOffst, 0)
        'put name in column B - offset 1 from column A
        rngCell.Offset(0, 0).Copy _
            Destination:=rngDestStart.Offset(intDestOffst, 1)
        'put status in column C - offset 2 from column A
        rngCell.Offset(0, 2).Copy _
            Destination:=rngDestStart.Offset(intDestOffst, 2)
        'increment destination row offset
        intDestOffst = intDestOffst + 1
    End If
Next rngCell
End If
're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
're-enable events
Application.EnableEvents = True
End Sub

From the Visual Basic menu click File - Save.
Click Alt + f11 (the Alt key and function key 11 pressed together)
This takes you back to the main Excel window.

Now enter a date in cell C1 on the Report page.
The data should now appear as shown.

As an extra, you can create a drop-down in cell C1 which gives you today and the last 7 days' dates, so that you can quickly select a date for a report.

On the report worksheet in cell K2 enter this formula

Drag the formula down to cell K9
Now select all the cells K2 to K9, right-click and select Format cells...
Format the cels with this custom format: m/dd/yy which exactly matches the date format you are using.
Click OK
Apply the same formatting to cell C1
Again with cell C1:
From the Ribbon select 'Data' and in 'Data Tools' select 'Data Validation'.
From the drop-down select 'Data Validation' and in the dialog box that opens select List from the 'Allow:' box.
In the 'Source:' box enter the range of cells like this:

Click OK
Now selecting Cell C1 will display a short list of dates to select from.


Report •

April 19, 2010 at 10:34:06
Humar Thank again for your help

This is the source data base that we fill up every day for Admission, discharges, Transfers and Expired patients
we have been doing these manual inputs for years is there a way to do this in marco to give me the report on the daily
activity eg. All admit, discharge, staus change and expired pt. on a single date like for 4/5/2010

The source are being entered on diffirent worksheets the the Report on Admissions . Discharges, Room and Status changes and Deaths are printed all together in one page or worksheet. Also in the report on the previous macro if you put the date on a day that has one patient it will also show the other patients the day before if there were more admit on the day before if not its fine fine is ther a way to fix these?

Please I need help


4/1/2010 Phillips, Frances AAA Medicare 5033-0
4/1/2010 Carolan, Bill BBB Private 4050-3
4/3/2010 Kawe, Rose CCC Insurance 4441-5
4/3/2010 Miller, Sysan ABAB Medicare 5454-0
4/5/2010 Franz, Ruth AAA Insurance 5345-0
4/5/2010 Brooks, John CCC Medicare 2345-4

4/1/2010 Cooper, Emma Home Medicare 324-0
4/3/2010 Cortese, Peter Home Insurance 432-1
4/5/20/1 June, May AAAA Private 6780-1

4/2/2010 Sokol, Bill 3000-2 Medicare 3454-0 Medicare
4/3/2010 Rickert,Paul4566-2 Insurance 4556-6 Insurance
4/5/2010 Franz, Ed 2304-0 Medicare 6767-0 Private

4/5/2010 Table, Kent Private 4545-0

(Report to be printed on one page)
Franz, Ruth Insurance 4345-0
Brooks, John Medicare 2345-4

June, May Private 4/5/20/1 6780-1

Table, Kent Private 4545-0

Franz, Ed 2304-0 Medicare 6767-0 Private

Report •

April 19, 2010 at 13:23:41

Can you provide the names of the different source worksheets and the ranges of cells containing each data element.


Report •

Related Solutions

April 19, 2010 at 14:32:27
Tab1. Admissions Month of April 2010
Tab 2.Discharges Month of April 2010
Tab3.Room and Status Change Month of April 2010
Tab 4.Deaths Month of April 2010

* I just put the month and year monthly
* I dont know if it will be better to put all in one worksheet
* We print out every month a report the Sources ( Monthly, Admissions, Discharge, Room and Status Change, and Death, so the file adds up daily. So if we have more than 100 hundred Admissions, Discharge, Room and Status Change, and Death for the month that will be the range. for that month and we start again for the next month . I was think if we can just put everything for the whole year then just print out the monthly report and daily report. If its possible using macro , the daily report is just basically print the daily activity . So I really do not know what will be the best range for the whole year. Sometime we have no Admissions, Discharge, Room and Status Change, and Death for the day.

What do you think will be the best thing to do? Thank you in advance again for being so kind and helpful.

Report •

Ask Question