Using Macros to format a pivot table (2003 SP2)

Topic

New Reply

Hello all,
I’m new to this lounge and also pretty new to the advanced features of Excel. I just took an Advanced class and the instructor gave us this site if we had questions/issues. Anyways, here’s my problem:

I work as a financial analyst at the design center of a truck manufacturing company. Each month, the accounting department posts pivot tables displaying actual hours and forecasted hours for each functional group (chassis, body, electrical, etc.) In the attachment, the sheet “Datafeed” is how the report is posted (this is the October ’06 report). I am trying to create a macro to format this report so it shows up like the report in tab “Formatted”. Basically, I want to see the actual and plan hours for the chassis department. And for each future month use this macro to format the table for me. I created a macro, but it states there is an error upon running it (I tried it in Datafeed2). Not to get ahead of myself, but once the report is formatted, I am going to try and create a macro that calculates an actual to plan percentage so managers can easily see where they over or under spent hours budgeted. But, first things first — is using a macro to format the table possible?

Thanks,
sulli101

*** I tried attaching the file, but it is too large***

Reply | Quote

Replies

Welcome to Woody’s Lounge!

If the file is too large to attach, try zipping it. The size limit is 100 KB.

Reply | Quote

Okay, try this…

Reply | Quote

Unfortunately, I can’t do anything with the pivot tables or the code, since the pivot tables have an external data source.

Can you tell us what error message you get, and on which line it occurs?

(BTW, I’d remove all the ScrollRow instructions at the end)

Reply | Quote

Thanks for looking at this. It looks like I’m going to have to format the table myself from the external source and then copy to a new worksheet. The attachment shows what I did. As you’ll see, I get a lot of DIV/0! errors in my actual/plan column due to zero hours planned for some groups. Is there a way to create an “IF” function that will have Excel input something like the highlighted text I wrote in the attachment for each error cell? Also, how can I create a rule that will automatically fill the background color of a cell based on its Actual/Plan percent? For example, all percentages over 300% will be colored red.

Thanks again,

sulli101

Reply | Quote

1) You could use a formula like this in G97:

=IF(F97=0,E97&" actual, "&F97&" planned",E97/F97)

This can be filled up/down

2) Select the range from G3 down to the end.
Select Format | Conditional formatting…
Select Cell Value Is from the first dropdown.
Select Greater Than from the second dropdown.
Enter 300% in the box next to it.
Click the Format button.
Activate the Pattern tab.
Select red, then click OK.
If you wish, you can add up to two additional conditions (for a maximum of three), each with their own formatting.
Click OK when done.

Reply | Quote

Howdy. Bill Jelen/Tracy Syrstad published a book, VBA and Macros for Microsoft Excel. One chapter is devoted to VBA and Pivot Tables. They strongly recommend using VBA for Pivot Tables, but creating the PT in code as an intermediate step only, eventually copying/pasting and formatting (all in code). I used their examples, then began applying that code to my own work. It really does work better than using Pivot tables. I encourage you to look at the book.

Reply | Quote