Auto Update Pivot Table and format wrap text

I have this code in a module, so as to refresh automatically a pivot table
in a sheet named "PrintSheet" it works fine in till I added some extra code
to format the cell D:D. What happens is now it tries to format every sheet I
open, I only what to format every time I click on the "PrintSheet" and
not in all the other sheets, and also need it to format from range D6:D not
the complete column.
 I found out that the pivot table does not keep its  format properties, so
when new data is added it need to be formatted everytime, that is why I like
to do this auto refresh/format.

Sub Auto_Open()
Application.OnSheetActivate = "UpdateIt"
End Sub
Sub UpdateIt()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
   ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True

'''Added Code to format D:D
 Columns("D:D").Select
    With Selection
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("D2").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
End Sub


Thanks for your help!


0
10/30/2003 3:45:10 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
335 Views

Similar Articles

[PageSpeed] 3

What version of excel are you using?

If you're using xl97 or higher, you may want to dump the .onsheetactivate and
start using the worksheet_activate (for that particular sheet).

Right click on the worksheet tab that needs this done
select view code
use the dropdowns on the right hand side to choose Worksheet and Activate.

Excel will help you out with the shell of the procedure:

Private Sub Worksheet_Activate()

End Sub

Move your code from UpdateIt into there.  (and kill the auto_open).

When you're done tweaking, your code could look something like:

Option Explicit
Private Sub Worksheet_Activate()

    Dim iP As Integer

    Application.DisplayAlerts = False
    For iP = 1 To ActiveSheet.PivotTables.Count
       PivotTables(iP).RefreshTable
    Next
    Application.DisplayAlerts = True

    '''Added Code to format D:D
    With Range("D6:D" & Cells(Rows.Count, "D").End(xlUp).Row)
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    
    'You really want this?
    With Range("D2")
        .Select  'really change the location of the activecell???
        .Locked = False
        .FormulaHidden = False
    End With
    
End Sub

Since this is code behind the one specific worksheet, it won't run unless the
worksheet is activated (from somewhere else).


But if you're running xl95, then you could modify your code like:

Option Explicit
Sub UpdateIt()

    Dim iP As Integer
    
    If LCase(ActiveSheet.Name) <> "sheet1" Then Exit Sub
    
    Application.DisplayAlerts = False
    For iP = 1 To ActiveSheet.PivotTables.Count
       ActiveSheet.PivotTables(iP).RefreshTable
    Next
    Application.DisplayAlerts = True
    
    '''Added Code to format D:D
    With Range("D6:D" & Cells(Rows.Count, "D").End(xlUp).Row)
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    
    'You really want this?
    With Range("D2")
        .Select  'really change the location of the activecell???
        .Locked = False
        .FormulaHidden = False
    End With
        
End Sub

It just checks the name of the activesheet--if it's not the right one, get the
heck out.







Kevin wrote:
> 
> I have this code in a module, so as to refresh automatically a pivot table
> in a sheet named "PrintSheet" it works fine in till I added some extra code
> to format the cell D:D. What happens is now it tries to format every sheet I
> open, I only what to format every time I click on the "PrintSheet" and
> not in all the other sheets, and also need it to format from range D6:D not
> the complete column.
>  I found out that the pivot table does not keep its  format properties, so
> when new data is added it need to be formatted everytime, that is why I like
> to do this auto refresh/format.
> 
> Sub Auto_Open()
> Application.OnSheetActivate = "UpdateIt"
> End Sub
> Sub UpdateIt()
> Dim iP As Integer
> Application.DisplayAlerts = False
> For iP = 1 To ActiveSheet.PivotTables.Count
>    ActiveSheet.PivotTables(iP).RefreshTable
> Next
> Application.DisplayAlerts = True
> 
> '''Added Code to format D:D
>  Columns("D:D").Select
>     With Selection
>         .WrapText = True
>         .Orientation = 0
>         .AddIndent = False
>         .ShrinkToFit = False
>         .MergeCells = False
>     End With
>     Range("D2").Select
>     Selection.Locked = False
>     Selection.FormulaHidden = False
> End Sub
> 
> Thanks for your help!

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/31/2003 12:51:12 AM
Sorry for getting back so late, but haven't had a chance to read the post.
I have Version 2002 but I'll be running the spread sheet on Excel 2000.
I'll tried the macro and it works nicely, I did not know if there was a
command
to make the auto_open from the module only target a specific worksheet, but
instead I  had to
add the code to the worksheet itself. I did try this an idea but never occur
to me
to take out the auto_open.
The part     With Range("D2")
                 .Select  'really change the location of the activecell???
                 .Locked = False
                  .FormulaHidden = False
Was a mistake.

Thank you
Kevin

"Dave Peterson" <ec35720@msn.com> wrote in message
news:3FA1B200.686D19EF@msn.com...
> What version of excel are you using?
>
> If you're using xl97 or higher, you may want to dump the .onsheetactivate
and
> start using the worksheet_activate (for that particular sheet).
>
> Right click on the worksheet tab that needs this done
> select view code
> use the dropdowns on the right hand side to choose Worksheet and Activate.
>
> Excel will help you out with the shell of the procedure:
>
> Private Sub Worksheet_Activate()
>
> End Sub
>
> Move your code from UpdateIt into there.  (and kill the auto_open).
>
> When you're done tweaking, your code could look something like:
>
> Option Explicit
> Private Sub Worksheet_Activate()
>
>     Dim iP As Integer
>
>     Application.DisplayAlerts = False
>     For iP = 1 To ActiveSheet.PivotTables.Count
>        PivotTables(iP).RefreshTable
>     Next
>     Application.DisplayAlerts = True
>
>     '''Added Code to format D:D
>     With Range("D6:D" & Cells(Rows.Count, "D").End(xlUp).Row)
>         .WrapText = True
>         .Orientation = 0
>         .AddIndent = False
>         .ShrinkToFit = False
>         .MergeCells = False
>     End With
>
>     'You really want this?
>     With Range("D2")
>         .Select  'really change the location of the activecell???
>         .Locked = False
>         .FormulaHidden = False
>     End With
>
> End Sub
>
> Since this is code behind the one specific worksheet, it won't run unless
the
> worksheet is activated (from somewhere else).
>
>
> But if you're running xl95, then you could modify your code like:
>
> Option Explicit
> Sub UpdateIt()
>
>     Dim iP As Integer
>
>     If LCase(ActiveSheet.Name) <> "sheet1" Then Exit Sub
>
>     Application.DisplayAlerts = False
>     For iP = 1 To ActiveSheet.PivotTables.Count
>        ActiveSheet.PivotTables(iP).RefreshTable
>     Next
>     Application.DisplayAlerts = True
>
>     '''Added Code to format D:D
>     With Range("D6:D" & Cells(Rows.Count, "D").End(xlUp).Row)
>         .WrapText = True
>         .Orientation = 0
>         .AddIndent = False
>         .ShrinkToFit = False
>         .MergeCells = False
>     End With
>
>     'You really want this?
>     With Range("D2")
>         .Select  'really change the location of the activecell???
>         .Locked = False
>         .FormulaHidden = False
>     End With
>
> End Sub
>
> It just checks the name of the activesheet--if it's not the right one, get
the
> heck out.
>
>
>
>
>
>
>
> Kevin wrote:
> >
> > I have this code in a module, so as to refresh automatically a pivot
table
> > in a sheet named "PrintSheet" it works fine in till I added some extra
code
> > to format the cell D:D. What happens is now it tries to format every
sheet I
> > open, I only what to format every time I click on the "PrintSheet" and
> > not in all the other sheets, and also need it to format from range D6:D
not
> > the complete column.
> >  I found out that the pivot table does not keep its  format properties,
so
> > when new data is added it need to be formatted everytime, that is why I
like
> > to do this auto refresh/format.
> >
> > Sub Auto_Open()
> > Application.OnSheetActivate = "UpdateIt"
> > End Sub
> > Sub UpdateIt()
> > Dim iP As Integer
> > Application.DisplayAlerts = False
> > For iP = 1 To ActiveSheet.PivotTables.Count
> >    ActiveSheet.PivotTables(iP).RefreshTable
> > Next
> > Application.DisplayAlerts = True
> >
> > '''Added Code to format D:D
> >  Columns("D:D").Select
> >     With Selection
> >         .WrapText = True
> >         .Orientation = 0
> >         .AddIndent = False
> >         .ShrinkToFit = False
> >         .MergeCells = False
> >     End With
> >     Range("D2").Select
> >     Selection.Locked = False
> >     Selection.FormulaHidden = False
> > End Sub
> >
> > Thanks for your help!
>
> -- 
>
> Dave Peterson
> ec35720@msn.com


0
10/31/2003 5:23:48 AM
Reply:

Similar Artilces:

Auto complete form field from separate list
I can't seem to find the answer with the paperclip. I'm designing a form in a worksheet. One field intended for the user t input a "city name" We may be using 20 or 30 cities in total. It woul be great just to have to type a few letters and smack enter. I kno I'm a bonehead cause I can't figure it out, because it's probabl something simple I just haven't figured out yet -- famdaml ----------------------------------------------------------------------- famdamly's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2938 View this thre...

Excel 2007 Pivot Tables
1. How can I create a Pivot Table from a text file, downloaded from a mainframe database? I have been told this could be done in previous versions of Excel. 2. If I get a new text file each day, how can I refresh the existing Pivot Table, without the necessity of recreating Thank you very much Marsh Import the Text file into Excel. -- Regards Dave Hawley www.ozgrid.com "Marsh" <Marsh@discussions.microsoft.com> wrote in message news:1C480551-AE4D-4E31-B6DC-DF527E532091@microsoft.com... > 1. How can I create a Pivot Table from a text file, downlo...

Document Map Format Issue
I have a large document that has about 230 pages in it. As I built the document I would use the document map pane to help manage the document. As the document grew in size, I started to have problems with Document Map. If the Doc Map window was open when I attempted to open the my document, the document map would not contain the proper information with respect to my headings. All text in the doc map window is left justtified and it appears to be randomly including test in the document map that are not designated header styles. If I closed the doc map window then closed word, ...

Cell formatting behaviour question
Hey guys, I have 2 related cases that I dont understand how to get Excel formatting to work: 1. If I imported or copied rows of data (numerical) from another file (Word or Access) into Excel, the data is presented as raw numbers in each cell. Now I apply a formatting (i.e. I want comma separation for thousands, etc.) and it will not show up. However, if i then double-click inside a cell (as if to edit the contents directly), then hit enter the formatting I want shows up... but only that cell. I have hundreds of rows and I really need to force excel to display the formatting i want immediately...

Converting access report to pdf format
hi I wrote a database that, among other things, prints a variety of reports for my clients. Once each report is done, I run a program called pdfWriter to convert each report to a pdf file. (When this program is installed, I select the printer called pdfWriter then print the file. It doesn't actually print a physical copy but saves the file in pdf format.) Then I e-mail these pdf reports to the clients. What I'm looking for are ways that I could automate this process from Access. Can this be done? If so, could someone point me in the right direction. Any clues would be grea...

Access2000: Update query
Hi I have a table p.e. MyTable with columns ID, F01, F02, ..., F12 I also have a view/query p.e. MyQuery, also with columns ID, F01, F02, ..., F12, where MyQuery.ID is read from MyTable (i.e. both table and query have same set of ID-values), and rest of fields are calculated based on data from other tables in database. Now I need to update MyTable with values for columns F01 - F12 from MyQuery for all ID's. Somehow I don't get it working. What will be right syntax? Thanks in advance! Arvi Laanemets Normally, you use something like UPDATE MyTable INNER ...

Pivot Table names
I have a pivot table and after some manipulation it is giving me what I desire. One question: is it possible to get rid of all the titles that start with "Sum of" and replace it with something else? Thank you. Right-click the field heading and select Field Settings, you can change it there. If you want to change Sum of Amt say to Amt, add a space at the end, else you will get a duplicate name error. -- __________________________________ HTH Bob <john.menken@ceridian.com> wrote in message news:dd61aba2-f441-4678-ae07-a182494b7911@33g2000yqm.googlegroups.com... >I have...

Update pass through query of report recordsource
I am working on upsizing my access backend database to SQL Server. I have linked all sql tables to the frontend and am now running into the issue of slow performance on one of the reports. One way to improve that I thought was to change the recordsource query (which is a saved query) to a pass through query. However there are form controls that drive the parameters. So I was wondering if a better way to do this is to create the sql query string in vba and then set the report record source. Is this a viable option? Is there a better way? Please ask any leading questions i...

Multiple Tables into one querie
Ok here it goes bare with me. I have a 4 tables Tbl 1 (basic inform) Exihibator ID Name Etc 1001 Jennifer 18 1002 Bob 17 Table 2 Steer Table Exihibator ID Entry Number Placing in Class 1001 121 2 1001 122 5 1002 253 1 Table 3 Swine Table Exihibator ID Entry Number Placing ...

Security Update for Windows Server 2003 (KB956744) Cannot Install.
For Windows Server 2003. This is a standard Windows Update that I try to install but get a message that it failed to install. I can't find any source to tell me why it won't or what I can do about it. I am not getting an error code of any sort - it is just listed under "not installed" when I get my update installation screen at the end of the installs. Can anyone help please? I downloaded the update manually from: http://www.microsoft.com/downloads/details.aspx?familyid=a37a2d8a-a5ce-4f06-bf07-8cafa16e7a59&displaylang=en Ran it, and then ran Check U...

Date Selection for Pivot Table
I build some pivot tables for our laboratuary. They contains daily analysis results. I want a solution to: 1-) User can enter week number and then daily analysis for that week appears day by day on a pivot table. Example: Input: 49 Output:29/11/2004---> 05/12/2004 on pivot table and regarding data 2-) User can enter month and than daily analysis for that month appears day by day on a pivot table. Example: Input 11 Output:01/11/2004--->30/11/2004 on pivot table and regarding data Thanks a lot ...

what is keyboard shortcut to auto fill option
what is keyboard shortcut to auto fill option Rozeta, Try selecting your range to be filled (Shift-Down, or Shift-Right, etc.), then Alt-e, i, d. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Rozeta" <Rozeta@discussions.microsoft.com> wrote in message news:FA64E288-A05A-450E-BF9F-EBA7B61B4476@microsoft.com... > what is keyboard shortcut to auto fill option Do you mean a key that corresponds to double-clicking on the fill handle? I'm not sure there is one. If you go to Help and type keyboard shortc...

Duplicating text in Word 2007
Hi, I've got a template I previously did for Word 2003 where I basically had several {macrobutton nomacro Type something here} fields. Now, in Word 2007, I see I have the option to actually add a Text content control, which looks fantastic :) BUT, is there a way of making it so that if I type something in one content control, the same text value is automatically added into the second one as well? Or even a third? How can I do that? Found out that if I insert a comment field and copy that field, they both work as I want! Yay! But, the comment field I insert via the Insert...

How can I update endnote cross-references in Word 2007 ?
I created a long document with many endnotes and some cross-references to these endnotes. When I insert a new endnote somewhere in the document, the following endnote numbers change but not the cross-reference numbers. This seems to be a bug in Word 2007 as it worked perfectly in my previous Word version. How can I update endnote cross-references in Word 2007 ? Ctrl+A, F9 should work in all versions of Word. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Joe56" <Joe56@discussions.microsoft.com> wro...

Converting Publisher Files to PDF Format
When I try to convert Publisher files to PDF format I get a message that says "The size of the paper you have chosen is too small to show the printer markings", but when my commercial printing guy takes my same Publisher files and converts them, it works. What am I doing wrong. I've already talked to Adobe and they think that my Acrobat Distiller is set up properly. I have Adobe Acrobat 6.0 Standard, Windows XP Professional, Publisher 2003 and an IBM desktop with 260 gigabytes of memory ediejensen wrote: > When I try to convert Publisher files to PDF format I get a mess...

update /delete external refs
I have written a macro and want to se it on older workbooks.This requires that a new page replaces an existing page.However all the references come over with the new page.I want to use edit/find replace all to replace [JUL02]Frame!C$6 with Frame!C$6 ie delete all references to original worksheet.(or some other way!) I have figured it out!.I copy the new page over to the old worksheet and then change source of links back to itself! >-----Original Message----- >I have written a macro and want to se it on older >workbooks.This requires that a new page replaces an >existing pa...

Custum Cell Format
I have an array formula which automatically summarizes all available values of a certain column, in ascending order. The summary is fixed to a maximum of 8 cells and in many cases it uses less than the available 8 columns. Is it possible to format the cells by showing a 0 when the result/cell is empty In other cells in the sheet the cells show empty instead of a zero by using the custom format of 0;-0;"" Now I want an empty cell to show 0 Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ----------------------------------------...

Cannot insert pictures in Pubisher. Text also disappears!
I have worked for several hours on a newsletter, now everything changes. Photos overlay on print and print disappears. Read the third FAQ here http://ed.mvps.org/Static.aspx?=Publisher/FAQs -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Disgusted" <Disgusted@discussions.microsoft.com> wrote in message news:421A5715-4D0A-4EC9-8C94-941B4B9DBA9F@microsoft.com... >I have worked for several hours on a newsletter, now everything changes. > Photos over...

How do I change the category axis number formatting in a Pivot Ch.
I have created a Pivot Chart from Excel data (Excel 2000) and I need to figure out how to change the category axis number formatting. Currently, the dates in the chart are showing up in long form (01/01/2005) and I would like to change this to something shorter like 1/01. I changed the formatting of the data in the original raw data and then in the Pivot Table, but neither of these seemed to change the Pivot Chart formatting. When I right click on the category axis, I do not get the option the change the number formatting (I do for the value axis however). ...

Language changes after update query
I have a data entry form where the user fills out text, numerical data, dates, etc. I created a button in the same form that runs a macro in background containing several update queries. At least 1 record changes from English to some other kind of language or font but it looks like Japanese or Chinese characters. I can't figure out why this happens and only on the that 1 macro. I would very much appreciate your input. TIA On Wed, 5 Mar 2008 10:10:01 -0800, Y2 <Y2@discussions.microsoft.com> wrote: >I have a data entry form where the user fills out text, numerical data, ...

Excel 2007
In Excel 2002 I built a simple line chart with 3 series, date bottom axis - the source date table was a huge worksheet of 1000 lines and 30 columns, but and my chart used 3 contiguous columns plus a non-contiguous date column. To update the chart daily, i simply added the new line to the worksheet, extended my selection box to cover that line and the chart was done. I had set up legend titles that were not the same as the related column titles. Now that I have converted to Excel 2007, none of this works - when I try to update the series data, the chart melts into one line and I lose ...

Conditional formatting sort of?
Is there a formula that can be written to highlight / shade a row or column. I know that conditional formatting is one way to do it however, I am having difficulty writing a formula that works. On a spread sheet I have data in A11:S387, in column C is either a "Y" or "N", if for example C12 is "N" I would like to highlight / shade the entire all of row 12. Any help would be appreciated. Michelle select row by clicking on the row number 12. Select Format | Conditional Formatting... Change Condition 1 to "Formula is" and in the box enter: ...

Getting form expression results to update to table.
The totals from the expressions in a couple fields on the form don't update to the table. They (the expressions) work correctly in the form, but they (the results of the expressions) don't appear on the table after clicking Update All. Does anyone know how ot fix this? Hi Peg, First, forms don't contain fields. They contain controls (most commonly textboxes) which may or may not be bound to fields in the form's recordsource. If they are bound to a field, changing the data in the form will change the underlying field in a table (unless the form's recordsource ha...

Saving Outlook Email As Text File Extract
Hello Outlook Group: I'm normally an Access and Excel VBA programmer, but my client wants me to scrape the text from a select email address and insert it into an Access memo column. Does anyone know how to configure Outlook (VBA approach would be fine) so that when an email is received in a selected account, the message would also be extracted to a directory as a text file and then the Outlook message would be moved to some history folder. Once the text file exists in a predefined folder , I will just have Access periodically check the folder for files, grab the text, ...

Personal Address book address will not change/update
Hello, I have Exchange 2000 and Outlook XP/2002. I have a personal address book. When I attempt to update the email address of an entry, it always returns to the original SMTP address. Is this a bug? Has anyone encountered this before? Thanks, Rich ...