Solved: generate reports.. need help...

[shahcu]

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]

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]

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]

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]

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]

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]

Yap!

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

Pls help

[JimmyTheHand]

Okay, then set the OnClick event macro of the report button as
[VBA]Private Sub ReportButton_Click()
Report1 (Userform1.Textbox9.Text)
End Sub[/VBA]
This should do.

Jimmy

[shahcu]

Thanx Jimmy....

It did the trick....

Regards,
Shahcu