• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automated Report Generation help needed

Asfandyar Ali

Asfandyar Ali

Member
Hello Narayan :) i must commend the forum. Excellent. I need help in automated report generation in Excel/VBA. Please see attached .xls sheet. I need to import data from "Data" worksheet to the "Report" worksheet.
The problem is i cannot do it via formula because;
  • if project code is "5512482" it has different set of "PO No.". i want to generate report on the basis of "PO No."
  • Also i want to list "Description" against each "PO NO."
P.S: I want to import Red highlighted cells from "Data" into red highlighted cells of "Report".
 

Attachments

Hi Ali ,

A few doubts ; I have added a sheet where I have tried to find the correlation between items on the DATA tab , and the items on the Report tab.

Can you confirm that everything is correct ?

What is to be done about the item tag Project ?

Narayan
 

Attachments

Dear Sir.
Please see attached confirmation with remarks in the sample file.
"Project" name should be displayed in "G5" of report worksheet with respect to the project code.
P.S: each project (cell BW1 in data worksheet) has specific code which is listed in "Project Code" column (cell AO1 in data worksheet).
 

Attachments

Hi Ali ,

Please note that if your requirement is urgent , I will not be able to help. Sorry.

As it stands , I still have some doubts over your requirement ; hopefully once these are sorted out , the coding will not take long.

1. You have mentioned that there will be one P.O. per report ; from your data , I can see there are 4 P.O.s. These have the following entries against them :

10210036164-1 : 5 entries
10210036164-10 : 15 entries
10210036164-104 : 1 entry
10210036164-108 : 6 entries

I assume that these 4 P.O.s will need 4 worksheet tabs ; is that correct ?

Secondly , since you have 7 entries against a P.O. in your template , this means that for the second P.O. , after the first 7 entries have been entered in your template , the template has to be copied down , and the next 7 entries have to be made in this ; for the last entry , a second copy will have to be made ; is this correct ?

2. You say that against Scope of work , it should be matched with the P.O. ; however , in your template , there is only one entry for Scope of work , for a P.O. ; your data shows multiple entries for Scope of work for a P.O. ; how is this to be taken care of ?

3. Against Subcontract No. you have mentioned that it could be distinct in data ; what does this mean ? If it is the same as for Scope of work , what is to be done ?

Narayan
 
Dear Sir.
It's not urgent. Please take your time :) I apologize in case i've created a mess here. Sorry for that!
I've tried to simplified the things; i've filled my requirement in the attached file, and i need data to be extracted from "Data" tab to the below mentioned worksheet tabs to make templates against each PO No.
10210036164-10
10210036164-102
10210038032-45
10210041453-16

I hope i've clarified the points in attached file.
 

Attachments

Hi Ali ,

I think what you want may not be possible.

We need to standardize on a template , which will have a fixed number of item rows ; in your file , one report has 15 lines , while another has only 3 ; this may not be possible.

Can we not standardize on 7 item lines per page , as your first uploaded file had ? So , if a project has , as you mentioned 60 items , it will cover 9 pages.

Narayan
 
Hi Ali ,

See if this is OK.

You need to run the macro ThisWorkbook.Create_Project_Reports ; there is no error checking.

When you run the macro once , it creates the respective project worksheets , and renames them to the project numbers. Since there is no error checking , please do not run the macro a second time , unless you have deleted the newly created tabs or renamed them.

Narayan
 

Attachments

Thank you so much Sir. initial test was v successful. i will get back to you once i finish with my original data :)
you are truly an expert :))
Bundle of thanks Sir. you rockkk :))
 
Dear Narayan Sir.
Thankyou so much. the code works like a charm. im now trying same code with same template but different header and it gives me error 400 on a message box. can you please guide me whats the issue.
P.S: Sample file is attached
Thank you.
 

Attachments

Hi Ali ,

See the file now. You will have to run the macro to generate the report sheets.

The problems were :

1. The named range Projects needed to be correctly defined ; in your first file , the P.O. number was in column G , whereas in this file it is in column W ; this change needed to be reflected in the definition of the named range.

2. In the macro , I am sorting the entire data range on the P.O. number column ; since this has changed from G to W , it needed to be changed in the code - instead of G2 in the following statement , it needed to be W2.

Range("DataSet").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("J2"), Order2:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers

Narayan
 

Attachments

You must log in or register to reply here.
Back
Top