• 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

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

  • SAMPLE FILE.xlsx
    84 KB · Views: 13
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

  • SAMPLE FILE.xlsx
    88.3 KB · Views: 10
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

  • SAMPLE FILE2.xlsx
    86.6 KB · Views: 10
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

  • SampleFile_New.xlsx
    189.6 KB · Views: 4
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 Sir.
You can consider 200 lines per PO template because i have largest PO of 152 lines in my data.
I've modified worksheet tab "10210036164-10" as an example.
 

Attachments

  • SampleFile_New2.xlsx
    201.4 KB · Views: 5
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

  • SampleFile_New2.xlsm
    275 KB · Views: 10
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

  • Onsite Sample.xlsm
    188 KB · Views: 9
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

  • Onsite Sample.xlsm
    191.6 KB · Views: 10
Back
Top