VBA / Macro help needed to create summary report from another sheet

[krjoshi]

Folks,

Please help me to create a summary report in sheet called “SUMMARY” from the master sheet “ALL JOBS”.

I have tried to create the same report by using pivot table option, but its giving lot of troubles since the data volume is huge, data vary all the times and sometimes Columns will come without header.

Then I have tried the same with COUNTA formula but this time I got another issue i.e. if I add new row data in the master sheet then I need to modify the formula each time.

I have attached the work book of what I am trying to accomplish.


Thanks in advance for your time and expertise

[xladept]

Hi KR,

Is it just the two tables ultimately?

[krjoshi]

Hi XLAdept,
Thanks for your reply. There are 3 more table similar to the existing ones. Please note that the row data will vary so each tables to get updated automatically. Please help me.

Thanks in advance.

Joshi

[jindon]

Try this

Please Login or Register  to view this content.

[xladept]

Hi KR,

It looks as though Jindon has solved your problem?

[krjoshi]

Hi XLAdept and Jindon,
Thanks for your reply, time and help. Jindon's code works excellent...! One small issue is the cell's formats getting collapsed while deletion or addition of new row data. Is there any chance to auto adjust the cells format too? And as I said in the previous post, can you please help me to generate two more reports in same sheet. Revised workbook is attached with report details.

Thanks in advance.

Joshi

[krjoshi]

Bump. .......Folks please help

[xladept]

Hi KR,

I'll look at it tomorrow as it's too late now.

[jindon]

I just don't understand what you are trying to do.

Wait for someone else.

[krjoshi]

Xladept / Jindon,

Sorry for bothering you both. I have attached the new workbook with more details as advised, this may give you better understanding. kindly lookt at once and please help me to fix these reports.

Thanks,

Joshi

[xladept]

Hi KR,

I've sort of adapted Jindon's code but you'll need to run both programs:

Jintest - virtually Jindon's test code and

Dontest - my awkward attempt to make things work.

*The last table doesn't agree with your sample but it jives with the data.

Please Login or Register  to view this content.

[krjoshi]

Hi Xladept,

Thank you so much, we almost there. some issues with your code.

1. When I change the row data in sheet ALL JOBS then re-run the code, the summary is not adjusting the row data. i.e. If I change all the "Main Task Actionee" to "OPD" then I re-run the code, the "Total" is appearing two times.
2. The Count Calculation is incorrect for Report No. 4
3. Row header (title) not filling automatically (like "No of Jobs", "Total", etc.)

Please see the attached workbook for better understanding. Kindly advise and help me please.

Thank you so much for your time and help.

[xladept]

Hi KR,

We need to settle on the format and set up of the SUMMARY sheet - I've preserved your formatting by using the set up that you had in the sample prior to this one. And, you need to check your own figures f.r. There are just 4 Field Maintenance entries not 8???

Thanks for the rep.

[FDibbins]

Joshi, do you still want this thread closed? Seems to me you pretty much have what you want?

Dear Moderators and Admin,
Please close this thread as I want to switching overt to our Commercial Service sub forum.

thanking you.

Joshi

[krjoshi]

Ford,

I will mark this thread as SOLVED, but I am switching over this thread to Commercial forum since I have some additional reports to be generated.

Thanks for your advise and support for jindon, xladept and ford. Thank you so much once again guys.

Joshi

[FDibbins]

OK then I will close this thread so you can continue in the CS forum