Keeping formatting of cells for pivot table 1

[willyboy58]

If I have cells in a spreadsheet that are formatted bold or with a specific font, how do I get this formatting to carry over into a Pivot table? I keep losing the formatting when the PT is created.

TIA

Bill

 

[Jooky68]

I am unclear what you mean by a pivot table..? Is it somethign you are creating within excel...if so you could always use the paste special. When you copy something and want to paste it somewhere else, try right clicking on where you want to paste it and go down to paste special. In there you can paste what you want, whether it be just the spreadsheet value, column width, format, etc... If you want to add that to your coding you can always just record a macro while you do that. I'm not sure if this is what your looking for so let me know...

 

[Zathras]

In Excel 97, when using the Wizard to set up the PT (Step 3), you can double click on a field which gives you a dialog where you can indicate how to summarize (Sum, Count, Average, etc.) -- you probably are doing this now. ("Pivot Table Field" in the title bar.)

Click the button marked "Number..." This will allow you to specify a format for that field.

Excel 2K has the same functionality, but I can't remember exactly where. "Look around."

 

[SkipVought]

Hey willy,

You could do something like this...

Sub FormatPT()
    With ActiveSheet.PivotTables(1)
        With .RowRange
            With .Font
                .Size = Range("MyNames")(1).Font.Size
                .Bold = Range("MyNames")(1).Font.Bold
            End With
        End With
    End With
End Sub

where the range MyNames is source data corresponding to the Row Data in your Pivot Table. If you have multiple fields with different format then we will need to go deeper.

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

Skip, thanks for the input. This is what I need to do.

When an employee takes time off we use the leave code 186. When they take a holiday, we use 188. There are numerous other job codes which are not an issue at this time. I can export the information that I need into an Excel spreadsheet such as:

Branch EmployeeID Name Date Job Hours
1 1234 bob 06-01-03 186 6.0
1 1234 bob 06-02-03 36078624 3.0
1 1234 bob 06-02-03 38123456 4.0
1 1234 bob 06-03-03 188 6.0
2 1472 steve 06-01-03 36078624 4.0
2 1472 steve 06-01-03 52122747 3.0
2 1472 steve 06-02-03 36078624 6.0
2 1472 steve 06-03-03 188 6.0

There are three branches and literally thousands of entries like those above in a month. What I have been doing is separating the branches (with help from people like you) and then creating a separate Pivot Table for each of the branches. It gives me the total hours for each individual employee’s work day as well as row and column totals for all. Bob would have seven hours on 06-02-03 and Steve would have seven hours on 06-01-03. All other days for both would be six hours (that includes Steve on 06-02-03). Leave and holiday are always six hours. Their row totals would be 19.0 for both.

If I cannot do any type of math or formatting on the PT itself (previous thread of mine), I was wanting to format the hours for the leave and holiday before the PT is created and transfer the formatted hours over to the PT. If they are bold, they will stand out. Currently, once I create the PT, I no longer can tell what the jobs and hours are for. Then once I get the bolded numbers over, I would count the number of bolded fields for each employee and include this on the report with the total days.

The other thought I had, which is much more complicated, was to read the job number and if it is 186 or 188 place an “L” or “H”, respectively, next to the hours on the PT. But then, I really can’t do any math b/c I now have a field such as “6.0H”.

Any thoughts will be greatly appreciated. TIA. Bill