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