New to the Forum - Automated Reports in Excel - Looking for Simple Solution

[planetb]

Hello Excel Gurus,

I am glad to be here. I am new to this forum and also to Excel. I am trying to explore this mysterious world of Excel and have just discovered that Macros can do wonders to day to day tedious jobs. I find it very fascinating but feel very restricted due to lack of skills. While I get more insight into this intricate world. I would like to request SMEs to help me solve this problem.
I have two Sheets, Sheet1 with employee data(listed below) and Sheet 2 is a report that gets generated for each employee that is listed in the sheet1 with the unique data elements for each employee in sheet1.(These data elements are highlighted in both sheets.)
Please provide some simple solution. Macro, Lookup, maybe something else.

Thanks. Appreciate any help. Look forward to the response.

Sheet1 and Sheet2 are attached.

[arlu1201]

You need to explain what you need to get done.

Are both these sheets part of 1 workbook in reality?

[planetb]

It could be part of same work book or separate file.

Sheet1 has data related to all employees...
Sheet2 is the report format that needs to be generated for each of the employees in sheet1 with the variables highlighted.

Every week a reports needs to be submitted for all the employees in sheet1.

Thanks for your response. Sorry I didn't get notification of this post. I may need to change the setting.

Appreciate your help.

[planetb]

Hi, Just a followup on if the thread explains the problem well or if I need to start another thread.

Thanks.

[jaslake]

Hi planetb

You probably don't need to start a new Thread. However, as Arlu stated, you do need to explain your requirements a bit better.

So, to help us understand your needs:

• In Book sheet1, complete worksheet sheet1 with info for 5 or 6 EMPNAME's. Include duplicates if that happens in your "real" world.
• Then in Book sheet2 complete sheet POD - xyz with what your expected results are for each EMPNAME.

Perhaps we can help.

[planetb]

Thanks for the response and apologize for not providing the complete requirements.

The original post is updated with the new sample files.
Sheet1 has data points for all the employees; Row1,2,3 holds data for employees.



Thanks.
Appreciate any help in thsi matter.

Sheet2 is the sample report that is sent for each employee.

Sheet2for row1 sample is attached. Highlighted cells are the one that gets populated for each report from sheet1. so end of the day there should be three sheets(reports) generated(or however many rows are there in sheet1); one for each row(employee)..

I hope this explains the problem. The control for the generation of the report can be part of the sheet2. So any time we click some botton the report gets generated for the next row in sheet1.

[jaslake]

Hi planetb

Will the SAME Employee EVER appear more than once in the Input File?
Will the SAME Employee EVER work on MORE than one SOW #?
Will MORE than ONE Employee EVER work on the same SOW #?

If any of the answers is YES please include samples of such in your Input File.
If any of the answers is YES I need to SEE how you want these situations handled in the Output File.

So, to help us understand your needs:
•In Book sheet1, complete worksheet sheet1 with info for 5 or 6 EMPNAME's. Include duplicates if that happens in your "real" world.
•Then in Book sheet2 complete sheet POD - xyz with what your expected results are for each EMPNAME.
Perhaps we can help.

[planetb]

The answer is yes to all. Please check the updated sample sheets.

There is also a slight change in the fields:There is a new field called clarity-id that is included in the input sheet.

The objective is to have one report for each unique SOW# & Clarity-ID combination and it should include data for all the resource(employees) that are working on that sow+ clarity-id combination. The data may be sortable by SOW# & Clarity-ID.

The combinition of SOW# and Clarity -ID will list all the resources(employee) working on that sow+clarity id.
Their hours, rates and dollar amount totalled in the report.

I have included the example to clarify this.

Thanks in advance. Look forward to jump starting this one me. I should be able to get more involved once I get a hang of it. Appreciate it.

[jaslake]

Hi planetb

Show me

Will the SAME Employee EVER appear more than once in the Input File?
Will the SAME Employee EVER work on MORE than one SOW #?
Will MORE than ONE Employee EVER work on the same SOW #?

If any of the answers is YES please include samples of such in your Input File.
If any of the answers is YES I need to SEE how you want these situations handled in the Output File.

[planetb]

Hi, The attachments have been updated in the original post. I have provided the samples of all the three scenarios you have mentioned. Hope it gets clarified little better this time. Thanks and sorry about the confuison.

Appreciate your help. Look forward to your response.

Thanks.

[jaslake]

Hi planetb

This is NOT simple

Please provide some simple solution

Try the code in the attached...please be aware...the code creates a workbook named Reports...it ALSO REPLACES all worksheets in Workbook Reports each time you run the code. You need to tell me how you want to handle that.
Place these two files in the same folder...run the code from the Button in POD Template.

[planetb]

Thanks for the reply.
I have saved the above two files in a separate folder but when I try yo run the code the follwing error comes up.

""Cannot run the macro from the c:\documents and settings\z125838\local settings\temporary internet files\content.ie5\osug8k18\podtemplate.xlms!create POD' the macro may not be avaialble in this workbook or all macros may be disabled.""

Appreciate your help on this.

[jaslake]

Hi planetb

I just did the same, downloaded the two workbooks into a new folder on my desktop, opened POD Template, clicked Options, enable macros.

Clicked on the Create POD button and the code ran as expected. The error message you describe would seen to indicate you're trying to run the code from the file on the Forum Website rather than from the file you downloaded.

[planetb]

The macro is running now with the following error:

Runtime error 1004
"Excel cannot insert sheets into the destinition workbook, because it contians fewer rows and column that source workbook. To move or copy the data to the destiniton workbook, you can select the data and then use the copy and paste commands to insert it into the sheets of another workbook."

[jaslake]

Hi planetb

What line of code is throwing the error?

[planetb]

Never mind!! The report did get generated second time I clicked create pod.

Can the program generate all the reports at the same time for all the SOWs+Clarity Ids combo ? or I can rename the generated report and run the program agian. Please advise.

Thanks.

[jaslake]

Hi planetb

I don't understand the question...the Code DOES

generate all the reports at the same time for all the SOWs+Clarity Ids combo

Are there some that are missing?

[planetb]

The following lline is generating the error.


ThisWorkbook.Sheets(1).Copy After:=tWb.Sheets("Sheet1")

[planetb]

Currently the program generates only one report. There should be reports genrated for all the SOW+clarity ids and each report should include all the employees that are working on that SOW+Clarity-id.

[jaslake]

Hi planetb

The code is written such that you'll get one Reports.xlsx workbook. Within that workbook will be a separate worksheet for each SOW+clarity id and will include all the employees that are working on that SOW+Clarity-id....at least that's what I get when I run the code...see attached.

Show me what you get.

[planetb]

Something is not correctly configured in my report.

When I run the code I get runtime error 1004. same as I sent previousely. I don't get the sheets as your report is showing.

in debug mode; line 94, col1 is giving the error.

ThisWorkbook.Sheets(1).Copy After:=tWb.Sheets("Sheet1")

Please provide some insight on what could be wrong in my environment.

[jaslake]

Hi planetb

Are you getting this error on the files I uploaded in Post #11? If so, I can't imagine what's wrong with your environment. If your running the code on different files then I'd need to see the files to debug.

[planetb]

Hello There,

Sorry to bother you so much. The code is working but the initial run is always throwing runtime error 1004. Same as I sent previously. I don't get the sheets as your report is showing.

In debug mode; line 94, col1 is giving the error.

ThisWorkbook.Sheets(1).Copy After:=tWb.Sheets("Sheet1

However, after I close all the sheets and reopen them, the code is running and generating the report similar to the one you have sent. Also I believe the data sheet needs to be open while I run the code.

Please provide some insight into this erratic behavior of the program.

Also, I need to understand the statement you mentione earlier. Will renaming the Report solve this problem.

"The code creates a workbook named Reports...it ALSO REPLACES all worksheets in Workbook Reports each time you run the code. You need to tell me how you want to handle that.

Thanks. Appreciate your time and wisdom.

[planetb]

I guess the program expects a empty Reports.xlsx file to be there in order to run. It populates the the existing Reports.xlms file and also cannot rerun if the file is already populated.

[jaslake]

Hi planetb
Not so

I guess the program expects a empty Reports.xlsx file to be there in order to run

If it does not exist, the code creates it.

Not so

I believe the data sheet needs to be open while I run the code.

If it's not open the code opens it.

If Reports File is NOT empty, the code empties it (except for Sheeet1)

I asked you previously

Are you getting this error on the files I uploaded in Post #11?

[planetb]

Yes. I am running the program on the same set of files you have uploaded. The error is generated on the same files. but only the first run, the subsequesnt run, the output report comes out fine. The only difference in two runs is the prescence of emplty report.xlms file or something beyond my understanding.

[jaslake]

Hi planetb

Let's try this again.

Exit Excel. Delete the Reports file. Open POD Template.xlsm and enable macros. Click the Create POD button.

Here's what happens for me:

The code looks to see if Reports.xlsx exists. If it does exist, all worksheets except Sheet1 are deleted. If it does not exist the code creates it.
The code then looks to see if Data File.xls is open. If its not the code opens it.
The code then looks in Data File.xls to see if a sheet named Lists exists. If it does not the code creates it. If it does exist it clears it.
The code then creates a unique list of Clarity-ID plus SOW # and places this list in Data File.xls in the worksheet called Lists and creates a Named Range called "Temp".
The code then filters Data File.xls for each record in the Named Range "Temp".
The code copies POD - xyz to Reports.xlsx (after Sheet1) and renames it to the Unique Clarity plus Sow # and populates this new worksheet with the Filtered Data.
The code does this for each Unique Clarity plus Sow # in the Named Range "Temp".
The code does NOT SAVE Reports.xlsx...this SHOULD be added but this WILL NOT cause any error.

Tell me what happens for you.

[planetb]

Unfortunately, the resutls are still same.

The program gives the error first time. Once the empty report.xlms file is in the folder, the program runs fine. The rerun of the program does not work if the report is populated. Once I rename the populated report and leave the empty report.xlms file, the program runs fine.

The data file may or may not need to be open. That doesn't impact the run of the program.

Please advise on what could be wrong.

Thanks.

Bushra.

[jaslake]

Hi Bushra

If I knew I'd tell you

Please advise on what could be wrong

Unfortunately I can't duplicate the error. The code runs as described on my platform...if I can't see the issue...I can't fix it...sorry.

[planetb]

The code is running fine for me as long as I have empty report.xlms file in the folder. So it is not a show stopper for now... may be the problem will resolve later.

Couple of other quesitons:

How should I proceed if I need to copy the code to a new template (Same format but with logos etc..). The report may have some other formulas etc. also.
How can I create each report as a separate file with unique name?

Thanks again. Appreciate your repsoense and help.

[jaslake]

Hi Bushra

The code in the attached does this

How can I create each report as a separate file with unique name

Regarding this

How should I proceed if I need to copy the code to a new template (Same format but with logos etc..).

If the file structures of the Template worksheet and the Data worksheet are identical to what we've been working with then it's a simple matter of copying the code from Module 2 of the original Template into a Module in the New Template.

You'll probably want to put the New Template and it's Data File into a separate folder...either that or rename the New Template's Data file to something else and change the name in the code.

Let me know how I can help.

[planetb]

Thanks for the response.Appreciate it.

The pod template v1 is behaving erratically. I have to run it several times and each time there is same run-time error as before but an output file gets generated. There are four output files generated. (I believe there should be more)Once the output files are there the program is behaving fine. I guess this issue is atleast consistent.


I am attaching the actual template that I need to incoorporate the code into. It has two tabs one tab has the same report as we been working with, the second tab has just the sum of hours and I believe the template already has formula for it.

This report should be generated for each sow+clarity-id; about 12 of them..
You may use the same data file and see you get this many reports.

I think the program doesn't have permision to create and write in the output file at the same time..may the need to change the directory permissions.

Please advise.

Thanks again. Have a good weeknd and very Happy New Year ahead.

[jaslake]

Hi Bushra

Regarding this

There are four output files generated. (I believe there should be more)

How many should there be? What are they? Best I can tell there's 4 unique Clarity-ID + SOW # numbers.

I can't duplicate this

The pod template v1 is behaving erratically. I have to run it several times and each time there is same run-time error as before but an output file gets generated

Regarding this

I am attaching the actual template that I need to incoorporate the code into

You had previously asked how to get the code into a new template...I provided a suggestion. Did you even try?

Regarding this

This report should be generated for each sow+clarity-id; about 12 of them..

What are they? Enumerate them...I count 4.

CC123_MOR2012-HS011
CC345_XXXX345
CC456_XXX678
CC123_XXX678

Regarding this

You may use the same data file and see you get this many reports.

Do you get "about 12"...I get 4.

Regarding this

I think the program doesn't have permision to create and write in the output file at the same time

Is that causing this issue?

The pod template v1 is behaving erratically. I have to run it several times and each time there is same run-time error as before but an output file gets generated

Is this an issue or isn't it? If it is fix it then see if the code runs as I described.

[planetb]

Hello there,I guess you are correct about the number of files. Sorry about the confusion. Also, I have not updated the new template with code. The reason I sent it to you to see if it may have some macros/ formulas that may conflict with your code. I will copy code to it and try to run. Will send the updates.

Thanks.

[planetb]

Hello,

I have updated the new template with the code from the earier template. The data file is still same. This part seems to be ok but the issue is that runtime error is still there and this time it is creating only one file. I am attaching the updated new template and the data file .Please run it in your environment and see if this one is giving the same number of files as earlier(4). I have also checked the permission etc..there is no issue with it.

Thanks.

[jaslake]

Hi Bushra

Why does your new Template have two worksheets?

[planetb]

The second worksheet provides the total of hours on the first sheet by each person for all periods.. This is a standard sheet that we use. There are some other formulas coded in sheet to total various field.

Thanks.

[jaslake]

Hi Bushra

I don't see this

The second worksheet provides the total of hours on the first sheet by each person for all periods

I'd guess you gotta show me.

[planetb]

Hi Bushra

I don't see thisI'd guess you gotta show me.

The second tab is totalling all the hours by everyone for that project for that duration. I feel it is redundant with the first tab but this is how the template is desinged and is being used as standard by everyone.

Please take a look at the updated sample template. It shows the second tab totally all the hours by everyone for that project for the whole duration.

Thanks. Appreciate your time.

[jaslake]

Hi Bushra

I see. I'll try to get back to this tomorrow.

[jaslake]

Hi Bushra

Try the Code in the attached POD Template v2.xlsm. As before, the Data File and the attached POD Template v2.xlsm File should be in the same Folder. Run the Code from the Button Create POD.

If the Data File is named something other than Data File.xls change it here before you run the Code

Please Login or Register  to view this content.

I get 4 new files, one of which is attached:

• CC123_MOR2012-HS011.xlsx
• CC345_XXXX345.xlsx
• CC456_XXX678.xlsx
• CC123_XXX678.xlsx

Let me know of issues.

[planetb]

Hello There, Excel Buddy,

So sorry for the late response. Some how I didn't get auto-notification for this reply. I have been also little swamped professionally/personally and didn't visit the forum.

I will try it tonight again and let you know.

Also wanted to check if you could recommend a good beginnner level book on how to use Excel as a data analysis tool. Something other than Excel for dummies or other commercial books. Will appreciate any help in this regard.

Thanks again. Will get in-touch again soon.

[jaslake]

Hi Bushra

I'd suggest posing this question

recommend a good beginnner level book on how to use Excel as a data analysis tool

in this Sub-Forum
http://www.excelforum.com/excel-prog...materials.html

[planetb]

Try the Code in the attached POD Template v2.xlsm...

i don't see the attachment.. Is it in the earlier post or just make the suggested changes into the code I have.

Thanks.

[jaslake]

It's attached to my Post #41

[planetb]

Hello There, Sorry to bother you with this. But Post 41 doesn't seem to ahve the attahment and also it is somehow chopped post. i cannot even reply to it.

Can you please attach the templates again. sorry about the confusion.

Appreciate your time.

[jaslake]

Hi Bushra

I can view the Files in Post 41...not sure why you can't.

I've restored the Files from a backup and attached them...I believe they're the same files. Hope this helps.

[planetb]

Thanks. Appreciate it.