Excell VBA to Format Worksheet

[jhabey01]

Hello and Good Morning,
I have attached a file which shows data that is unformated. (see From WorkSheet). I am trying to format the worksheet to look like the To Worksheet using VBA. Here are some notes and that I have:
1) For each change in Office Location I need to insert two rows and sum the Total Premium in Column H.
2) Sum the Total Premium for each Office location into a Total at the Bottom.
3) Format the remaining report to look like the To worksheet.

This report is dynamic in that sometimes a new report will have all four products and sometimes 1 or 2 so the number of rows may be diffrent with text new report.

This is part of a bigger project in which I currently format 40 reports manually to look like the To worksheet.

Is something like this even possible?

Thanks,
John

 

[SkipVought]

Hi,

Two possibilities using built-in features that require no VBA.

1) The PivotTable feature
2) The Subtotal feature, not to be confused with the SUBTOTAL() function.

you might end up using a bit of code to kick off a Refresh.

 

[SkipVought]

Here are some additional comments.

Is Note 1 that you actually need 2 rows, and if so why, or is it that you need white space that can be accomplished using an appropriate RowHeight?

What happens with multiple Company values with your totals?

The PivotTable feature will do all these aggregations automatically!