Pivot Table Grand total issue need two grand totals

[alesha711]

Ok, so this is a complicated spreadsheet, that my client HAS to have. Let me explain the sheets and then I will explain what we do now. What's going wrong, and maybe you can help?

For security reasons I have changed all employees names to 1,2,3 etc. In my original they have names.

New-Report - The Report my client looks at
New-Pivot - the Pivot table
New-Data - the data we import from a payroll company


So our process of this sheet is to import the new data every pay check into the New-Data tab. Then we refresh the pivot table. THEN (this is where it get complicated)
In the New Pivot tab we open the Clipboard. Then we filter out per department using the filter box I have set up at the top of that page and copy. After we copy EACH of the departments then I select ALL of the departments, go to the very bottom and copy just the grand total. Then I go to the Report tab and go to A6 cell and select paste all in the clipboard dialog box. Well since (I guess it so much data) when I go to paste all it is not grabbing all the items in the clipboard. I tried selecting just half or just 3-5 departments and it still is not working correctly. Copy and pasting individually is taking WAY to long because we have to update this every 2 weeks. I deleted out 2/1/18-present (Had to delete a lot out so I could import here, then I realized I could save as binary workbook hopefully you get the gist). So question:

WHY DID THE CLIPBOARD PASTE ALL STOP WORKING CORRECTLY!?
and is there and easier way? I tried (when I originally set up the spreadsheet just doing the pivot table but I need a grand total per department and a grand total of all departments based on the Pay Codes. So to achieve this, this is why we copy and paste because you can't put a total in a total in a pivot table.

ANY HELP IS GREATLY APPRECIATED!!!!

[Pepe Le Mokko]

Title much more descriptive. Thanks

[Roel Jongman]

I had a look at your problem. to summarize. you are doing a lot of manual work to get custom totals under each department. for insiders, there is a custom sum on the innerfield "Earning code" those totals need to show under each department and not only at the bottom of the pivot for all departments.

I think resolving the clipboard issue is not very usefull building a report should not be so much manual work..
The only way I can see this done faster is to use VBA to rebuild the report from scratch each time new data is added. that is what I have done.

I build 4 macro's

1 clear the oldreport and rebuild the new headers on new report tab
2. one to copy the pivotdata for each department to the new report tab
3. Apply layout to department headers and grandtotal rows
4. a simple macro that start above three macro's after eachother

the codes...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Have a look at the attachment to see if this code solves your problem.. It will take about a minute or so to build the report because it has to step thru each department pivot and copy it. so do not expect an instant result

[alesha711]

That works GREAT! But I am no seeing the grand total at the bottom? Also how after the last payroll in the column I have a total Column. I totally forgot she asked this yesterday. Is there a way to add 2018 Total and then have a 2019 running total beside it?

[alesha711]

Sorry I wasn't clear on that, so for instance all the Departments total of pay codes at the very bottom underneath the last department.

[Roel Jongman]

Ok, I forgot about the grandtotal for departments that is in now..

To also add year totals I have changed the Pivottable to include the grouping per year.
this is then automaticly copied over to the report.

see the attached file with the updated code

[alesha711]

That is wonderful!!! Thank you so much! Now to recreate this for the 4 facilities they have... Also one more. It changed the formatting of the dates and I can't seem to get it to change back? Thoughts?

[Roel Jongman]

To change the date format right-click on a date in the pivot column headers, select fieldsettings then find numberformat button left-bottom of the settingsmenu. Then you can pick the date format you want to use.
after you set it correct in the pivot it will copy over.

[alesha711]

Yes, I tried that its not changing it.

[Roel Jongman]

You may need to ungroup first so it is "clean dates" and then regroup after change.
I always struggle with it a bit. I did not touch the date format, I kept it as it was to avoid troublel. But it just automaticly changed after I put year group on.
So if logic applies it should also change automatic if you ungroup and group again.

and maybe refresh the pivottable after ungrouping and before changing the format and regrouping

[alesha711]

You may need to ungroup first so it is "clean dates" and then regroup after change.
I always struggle with it a bit. I did not touch the date format, I kept it as it was to avoid troublel. But it just automaticly changed after I put year group on.
So if logic applies it should also change automatic if you ungroup and group again.

and maybe refresh the pivottable after ungrouping and before changing the format and regrouping

still not working. WEIRD

[alesha711]

Found out why.

https://www.excelcampus.com/pivot-ta...ld-formatting/

Ill just add a year column to my table no worries! No need to create yet another macro like the article says. Thanks Again!

[alesha711]

One more and I should have it. It keeps messing up my headers in Report Tab Row 4 & Row 5. Which macro could I manipulate to fix this? All is good Except 2018 Totals and the pay period after that keeps saying Total instead of the pay date. This is in a new sheet after adding 2/1/18-present payrolls in.

[Roel Jongman]

arch... strange functionality Excel has sometimes. I live in a d-m-yyyy area so never noticed that problem.. weird.

yeah the adding of the year column is the better option anyhow.. I often do that in my datasets as it also helps you to use year as slicer


Edit:
BuildReportHeaders() handles setting up the headers.

[alesha711]

Sub BuildReportHeaders()
Sheet1.UsedRange.Clear 'clear old report
Sheet2.Range("A2").Resize(2, Sheet2.UsedRange.Columns.Count).Copy Sheet1.Range("A4")
lcol = Sheet1.Range("A6").CurrentRegion.Columns.Count

With Sheet1.Range("A4:A5").Resize(, lcol)
.Interior.Color = RGB(180, 180, 180)
.Font.Bold = True
.Offset(, 3).HorizontalAlignment = xlRight
End With

With Sheet1.Range("A5").End(xlToRight)
.Offset(-1, 1).Resize(1, 2).Value = "Total"
.Offset(, 1).Value = "Hours"
.Offset(, 2).Value = "Amount"
End With

Sheet1.Range("A5:C5").ClearContents

End Sub

What would I edit? Sorry I am very Inexperienced in Macros

[Roel Jongman]

no problem.. That look like the old macro.. when the year field was not in the pivot yet.

this one should handle the new extra headerrow with year in the pivot right

Please Login or Register  to view this content.

edit: also attached the file again with the new year column..

[alesha711]

Still not working... I attached the one I made. See Column 2018 totals and the pay code after? when I run the Macro it won't put them as the right one. Just keeps putting Total Total for Pay 1/14/19 and Has just 2018. Makes no sense because all the other headers are correct.

[Roel Jongman]

It does make sense I tested with only 2018 data and forgot that 2019 data would also use many columns so that the grand total of all years is not right after 2018 totals
I did make the assumption that there will be 2 years max so I just repeated the with for 2019. If there are more than 2 years than might need to create a loop to handle the year totals

this is the new code
also I put some explanation of how those total columns are created or better renamed

Please Login or Register  to view this content.

[alesha711]

It does make sense I tested with only 2018 data and forgot that 2019 data would also use many columns so that the grand total of all years is not right after 2018 totals
I did make the assumption that there will be 2 years max so I just repeated the with for 2019. If there are more than 2 years than might need to create a loop to handle the year totals

this is the new code
also I put some explanation of how those total columns are created or better renamed

Please Login or Register  to view this content.

Works Marvelously!!! Thank you so much for your hard work! Everyone in my office says I am an excel Expert looks like I need to take a class on Macros and they will really be amazed HA! will mark as solved!