Help creating a button and report in Excel 2007 workbook

[mishmish3000]

Hi! I'm developing an Excel 2007 workbook for some nurses. They enter the date they are assigned the patient's case in one of the spreadsheets (DateHDNotified). They'd like a button on the spreadsheet to click that will print out a report listing patient names and numbers for those cases that are 2 weeks old.

For example, if they got a case today, 9/2/2010, they'd like to see the patient's name, number, and test results in a report on 9/16/2010. This report could be an additional spreadsheet in the workbook or it could just print out as a PDF, whichever would be easier to program.

Any ideas? I'd sure appreciate the help. I'm more used to working in Access, but the nurses all want Excel. :) And they know a heck of a lot more about nursing than I do, so I'm agreeing with them!

[HansV]

You could use Advanced Filter to copy the data for two weeks ago to another sheet, and print that.

See the attached sample workbook. It print previews the sheet, but you can easily change PrintPreview to PrintOut. (You must enable macros, obviously)

ReportSample.xlsm

[mishmish3000]

Thanks, I'll try it out, HansV!!!!

[mishmish3000]

Hans, or anyone else--have mercy! Help me! I tried to recreate the cool spreadsheet with the code, button, advanced filter... and it's not behaving for me. I'll attach screen shots of what I see. I'd attach the spreadsheet, but then I'd have to de-identify a bunch of patient data. Hopefully the screen shots will clue you in as to what I'm doing incorrectly.
Thanks!!!
MishMish

[HansV]

Fortunately, this is very easy to solve: I had assumed that the command button would be on the Data sheet, so I used ActiveSheet to refer to the Data sheet.
You have placed the button on the Report sheet, so ActiveSheet refers to the Report sheet instead of the Data sheet.
To correct this, replace ActiveSheet with Worksheets("Data")
Hopefully, it'll work OK then.

[mishmish3000]

Well, I did that... but it still stops when it tries to run the macro. ??
The macro code now says:

Option Explicit
Sub ReportCases()
Worksheets("Data").Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("Report").Range("Criteria"), _
CopyToRange:=Worksheets("Report").Range("CopyToRange")
Worksheets("Report").PrintPreview
End Sub

[HansV]

Have you named the range A1:G1 on the Report sheet CopyToRange, and have you named the range K1:K2 on the Report sheet Criteria?

If not: naming is very easy. Select the range, then type the name in the address box on the left hand side of the formula bar and press Enter.

[mishmish3000]

pls see attached... sigh!

[HansV]

You have accidentally removed the space and underscore after the line

CriteriaRange:=Worksheets("Report").Range("Criteria"),

It should be

CriteriaRange:=Worksheets("Report").Range("Criteria"), _

[mishmish3000]

Well, d'oh! LOL
it works now... but now it works TOO well. It prints all the records instead of just the one that meets the criteria (I thought). It's the last record in the Data sheet... that's the one that should print out, don't you think, if we're looking for records that were added two weeks ago today?
I'll check in this weekend to see if anyone had an epiphany.
Take care,
MishMish

[HansV]

Are you sure you're previewing the Report sheet?

[mishmish3000]

I'll check... thanks for the hint! We're almost there! Success in sight... I hope!
Mishmish

[mishmish3000]

Option Explicit
Sub ReportCases()
Worksheets("Data").Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("Report").Range("Criteria"), _
CopyToRange:=Worksheets("Report").Range("CopyToRange")
Worksheets("Report").PrintPreview
End Sub

I'm pretty sure I'm previewing the Report sheet. For some reason, when you enter today's date in the Date field in the Report sheet, and then click on the Print Report button, you see all the records from the Data sheet, not the record that is actually dated from two weeks ago today. Any ideas?
MishMish

[HansV]

You shouldn't enter today's date in the Report sheet - K2 should contain the formula =TODAY()-14

If that doesn't work, I'm afraid the only way to solve this is that you create a copy of the workbook, delete all personal information from the copy, and attach it to a reply (zipped if necessary).

[mishmish3000]

OK, I wasn't clear... the formula =TODAY()-14 is in K2. I don't enter today's date there. But no matter... with K2 populated as shown, when you click on the print report button to see the print preview, you'll see a report with all the fields, not just the correct entries.
Mishmish

[HansV]

So - could you post a sanitized copy of the workbook?

[mishmish3000]

here ya go!

[HansV]

The text in cell K1 on the Report sheet should be the exact name of the field on which you want to filter. So assuming that you want to filter on DateHDnotified, K1 should contain DateHDnotified instead of Date.

[mishmish3000]

okey dokey I'll try that then!