Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

PivotTable Formatting Issue Excel 2007


  • Please log in to reply

#1
janetlyn41662

janetlyn41662

    New Member

  • Member
  • Pip
  • 4 posts
I have a "timecard" sheet set up with a column of numbers that when a number is typed in, it does a Lookup and fills in a wide column with a name. Then a bunch of smaller columns, one for each day. To the right of that, there is a pivot table summary which updates everytime the timecard is opened, summarizes the numbers typed in, with the total numbers of hours put on that particular row number, no matter what day it was input.

I have manually set each timecard up for all employees at a height of 18". Most of the timecards, when opened, shrink the lines to 12.75, which is too small. However, there are a few timecards that keep the 18" spacing. I have tried everything to make the rows stay at 18" and cannot get it to work on some of the cards.

The only way I know to supposedly make it work is to make the rows all 18". Then right-click on the pivot table and select "Pivot Table Options", put a check mark in "Preserve cell formatting on update", save and then close the worksheet. However, even though I do this, it still shrinks the rows.

After MUCH searching I found the only to "fix" the problem is a workaround using Macros. I recorded a macro that selects each sheet, highlights the rows, changes the height to 18". I then put it in VB so when the worksheet activates it runs the macro. I finally got that to work, only the macro went first, then the pivot table updated, so my rows went back to tiny again.

I have tried several different ways to do the following, all for naught:

Open Workbook
Select Sheet 13 (sheets named SUM, DEC 09, NOV 09..........JAN 09) Jan is 13.
Refresh Pivot table 1, there is only one pivottable per sheet.
Run Macro "RowHeight"

Select Sheet 12.....and on and on until all sheets but number one are selected.

Right now, I am just trying to find the correct code for sheet 13 and then I will add the rest of the code for all the other sheets. At this time, this is what I have:

In the window: This Workbook, left drop down is "Workbook", right drop down is "Open"

Private Sub Workbook_Open()
   Worksheets(13).PivotTables(1).PivotCache.Refresh
ActiveWorkbook.RunAutoMacros xlAutoOpen
End Sub

I would appreciate any help I can receive. Thanks in advance, JL
  • 0

Advertisements







Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP