Automate Attached Report

[pr4t3ek]

Hi,
I create the attached report "Final" which is how I would like the report to be generated. This report is created everyday so the amount of data fluctuates. (hence why i would like to create a macro for this)

What i was planning on doing was to add the "data" to the "DATA" tab, and for the macro to generate the rest of the report... So a macro to fill out the "Summary" page

How I generate "Summary" page:
I create a pivot table on the data page. I want to see the "A# and Name".."Team Leader".."Centre"..."The Count of Key" & Case Category"

In the summary page the tables which have a blue heading are the "I106" case category... and with the yellow heading "ID14"..
With the Pivot Table I then sort it by descending by the count of key.... and then by the Centre so we then have the largest amount of key's per centre from the top...

The Orange total's should update accordingly and the cells between the total amounts and the last "A# and Name" to be deleted so it looks good.
We need to paste the records as special first to keep the formats in the cells.

And the pink totals to update automatically also...

Please see the "Blank" xls file to see how i would "start" off the report...

hope this makes sense, i am assuming more understanding may be required. Just let me know.

thank You

[pr4t3ek]

So i've got this now:
This creates the I106 Pivot Table

Please Login or Register  to view this content.

But the above would need to be amended i think, because there aren't always 70 I106's, there can be more or less.

This code copy's the contents from the pivot and paste's it as text and sorts it by descending and then by centre

Please Login or Register  to view this content.

Any idea how I could go about adding the result of the above to the relavent tables in the Summary page?

thanks

[rylo]

Hi

Here's a slightly different approach that will get your data. You will have to work on the formatting aspects as, to be blunt, I hate doing them!!! LOL

Hope it gives you some ideas anyway.


rylo

[pr4t3ek]

hey mate,
I've taken the attached approach and i like it lol..

but im just having an issue with those blanks after you run MakeID14Pivot ...... any ideas how i can get that fixed?

cheers

the macro's are attached to the file.

let me know boss

[rylo]

Hi

As you have your starting point for each of the blank deletions, don't select the blanks. Using DeleteID14MELBlank() as an example try changing it to

Please Login or Register  to view this content.

rylo

[pr4t3ek]

awesome stuff!
finally got this automated..
I've just got two more sheets which are pretty similar to the above on the same workbook but ill work on that in the next few hours.
have a good weekend mate! cheers! =]

[pr4t3ek]

okay so the above is sort of fine.
But the issue is that sometimes there is only one record or no records so the below code

Please Login or Register  to view this content.

doesnt work

I've got the attached sheet and im trying to do the "Summary-SystemError" tab but the issue is that for UCMS I106 there is the only one record so the code

Please Login or Register  to view this content.

goes all the way down to 65536 and that does not work.

What do i do :S..

Please see attached and run the Macro: MakeSummarySystemErrorI106Pivot

Thank YOU

[rylo]

PD

How about something like

Please Login or Register  to view this content.

rylo

[pr4t3ek]

awesome.
But in this instance.
CAN I106 does not have any so it copies the heading "A# AND NAME Team Leader Centre Total"
Is there a way for it to not copy this and just have like a blank cell on the report...
if you know what i mean..

thats not too important though...i can do that much manually! lol...

but yeah...there must be a way rite...

do u understand what i mean? if not, let me know & i'll explain..

thanks mate

[rylo]

Hi

OK how about

Please Login or Register  to view this content.

rylo

[pr4t3ek]

im getting a debug error unfortunetly with the below code

Please Login or Register  to view this content.

same, CAN I106

[pr4t3ek]

im getting a debug error unfortunetly with the below code

Please Login or Register  to view this content.

same, CAN I106

[rylo]

PD

In future, can you please advise where the error is occurring. However, try this

Please Login or Register  to view this content.

rylo

[pr4t3ek]

Hi Rylo,
I tried the above code but now it does not copy everything.
I used the code:

Please Login or Register  to view this content.

on all the copypaste modules and it just does not copy.

please see attached and run the macro's on the workbook. You'll see what i mean
thank you

[rylo]

Hi

oops

Try changing the set component to

Please Login or Register  to view this content.

rylo

[pr4t3ek]

hi,
thanks for that

yeah it works fine..

i guess the problem is my subroutines which delete the blank cells....which is correct but if there isnt actually any data in one of the catogories it still deletes the blank cells,

i guess i can put an if statements, if there is no data inserted then delete all cells in the catagory except one cell.....

anyway, cheers mate!
much appreciated, thank you