PDA

View Full Version : Solved: generate reports.. need help...



shahcu
03-22-2007, 10:45 PM
Hi!

I am in need of help..

I have an excel worksheet that contains several records. I would like to generate two reports using a macro which allows me create a new workbook with only one worksheet. I have created a userform that have two command buttons

1. Agent Report 2. Overall report

I need two reports out of it:
1: AgentName wise report which is sorted on the basis of Optis Date (Column available in Sheet1)

2. Overall report that is sorted on basis of agent name and Optis Due date

Enclosing a sample data I have columns as shown in sheet1. I need report (agent wise) in the format available in sheet 2 and overall report as per the format available in sheet3 of same workbook.

Please help

JimmyTheHand
03-22-2007, 11:50 PM
Hi :hi:

Run macros called Repor1 and Report2 in the attached workbook. Is it what you need?
BTW, I assumed that "Optis Date" was the same as "Optis Due Date".


Jimmy

shahcu
03-23-2007, 12:36 AM
Thank you very much Jimmy for your time and the resolution.

Both the macros work great. However, I need some more help from you.

The agent reports that run in Report1 takes all the records from sheet1 however I need for a specific agent report. Say if the Agent Name is ?Nancy? then in agent report it will only display the records entered by Nancy and not the complete one. For complete records we have Overall report.

JimmyTheHand
03-23-2007, 03:07 AM
I modified Report1 so that now it requires a string parameter: the name of the agent.
You can supply the macro with the agent name in any way you wish. I created another macro, called "test", which uses Inputbox function for this purpose. See the attached file.

Jimmy

shahcu
03-23-2007, 03:24 AM
Thank u very much to get back to me. I really appreciate your time and valuable support.

My applogilies that I have not mentioned the exact problem. Actually What I need is :

I have a user form that contains the User ID and Pwd for the user. Once the user logs in he can enter data thru form which ultimately get's stored in Excel sheet (Sheet1). Sheet1 also contains data stored by another users...

On the Main form I have a report button What I m looking for is that as and when a single user clicks on report button he will get all those records entered by him. SO there is no need of Input box ... Hope this will help u to understand my problem in detail....

Once again thanks a lot .

Please help ..

JimmyTheHand
03-23-2007, 04:17 AM
The macro called "Report1" must be supplied with the User ID as a parameter. For this, the current user's ID must be stored somewhere, after the user logs in. Where is it stored? In a public variable? In a cell? In a textbox of a form? Or?

Jimmy

shahcu
03-23-2007, 04:46 AM
Yap!

When the user logs in the Agent name will get store in UserForm1 textbox9... which is an another form....

Pls help

JimmyTheHand
03-23-2007, 04:53 AM
Okay, then set the OnClick event macro of the report button as
Private Sub ReportButton_Click()
Report1 (Userform1.Textbox9.Text)
End Sub
This should do.

Jimmy

shahcu
03-23-2007, 05:50 AM
Thanx Jimmy....

It did the trick....

Regards,
Shahcu