Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Setting print area to pivot table?

  1. #1
    Join Date
    19th September 2004
    Location
    Chester
    Posts
    148

    Setting print area to pivot table?

    I think the title just about covers it here! I'm currently using "currentregion.address" but it onlt seems to pick up one row from the pivot tables has anyone got any Ideas?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,676

    Re: Setting print area to pivot table?

    This might be more long-winded than necessary... but

    VB:
    Sub set_PT_print_area() 
        Dim rngTopLeft As Range, rngBotRight As Range 
        Dim lPTcells As Long, strAddress As String 
         
        With ActiveSheet 
            lPTcells = .PivotTables(1).DataBodyRange.Cells.Count 
            Set rngTopLeft = .PivotTables(1).RowRange.Cells(1) 
            Set rngBotRight = .PivotTables(1).DataBodyRange.Cells(lPTcells) 
            strPTAddress = rngTopLeft.Address & ":" & rngBotRight.Address 
            .PageSetup.PrintArea = strAddress 
        End With 
    End Sub 
    
    

    seems to work for me
    Last edited by Will Riley; August 22nd, 2005 at 17:46.
    Kind Regards, Will Riley

    Web Presence:
    Personal: The Trouble With Data
    LinkedIn: Will Riley

  3. #3
    Join Date
    19th September 2004
    Location
    Chester
    Posts
    148

    Re: Setting print area to pivot table?

    Thanks for that, but it seems to select and the cells in the sheet!!!!!!!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th September 2004
    Location
    Chester
    Posts
    148

    Re: Setting print area to pivot table?

    code in use, (Called from Userform)

    VB:
    Private Sub signed_btn_Click() 
         
        Dim rngTopLeft As Range, rngBotRight As Range 
        Dim lPTcells As Long, strAddress As String 
         
         
        Sheets("outstanding").Select 
        ActiveSheet.PivotTables("newoutstanding").RefreshTable 
         
         
        With ActiveSheet 
            lPTcells = .PivotTables("newoutstanding").DataBodyRange.Cells.Count 
            Set rngTopLeft = .PivotTables("newoutstanding").RowRange.Cells(1) 
            Set rngBotRight = .PivotTables("newoutstanding").DataBodyRange.Cells(lPTcells) 
            strPTAddress = rngTopLeft.Address & ":" & rngBotRight.Address 
            .PageSetup.PrintArea = strAddress 
        End With 
         
         
        Range("A2").Select 
         
        Me.Hide 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,676

    Re: Setting print area to pivot table?

    What is the value of the variable straddress ?
    Kind Regards, Will Riley

    Web Presence:
    Personal: The Trouble With Data
    LinkedIn: Will Riley

  6. #6
    Join Date
    19th September 2004
    Location
    Chester
    Posts
    148

    Re: Setting print area to pivot table?

    Sorted! it was the old typo error............

    VB:
    With ActiveSheet 
        lPTcells = .PivotTables("newoutstanding").DataBodyRange.Cells.Count 
        Set rngTopLeft = .PivotTables("newoutstanding").RowRange.Cells(1) 
        Set rngBotRight = .PivotTables("newoutstanding").DataBodyRange.Cells(lPTcells) 
        strPTAddress = rngTopLeft.Address & ":" & rngBotRight.Address 'strPT address don't exist!
        .PageSetup.PrintArea = strAddress 
    End With 
    
    
    That what you get when you just copy and paste any old code in without checking it!

    many Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,676

    Re: Setting print area to pivot table?

    Oops sorry, that was my fault!
    Kind Regards, Will Riley

    Web Presence:
    Personal: The Trouble With Data
    LinkedIn: Will Riley

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Setup A Print Area For A Pivot Table
    By lolo70 in forum EXCEL HELP
    Replies: 2
    Last Post: February 6th, 2008, 09:22
  2. setting print area in macro
    By Ray Latchford in forum EXCEL HELP
    Replies: 4
    Last Post: July 17th, 2006, 17:52
  3. Setting up the print area to default
    By ajay_singh in forum EXCEL HELP
    Replies: 3
    Last Post: June 2nd, 2005, 20:26
  4. VBA setting print area
    By weaholt in forum EXCEL HELP
    Replies: 2
    Last Post: February 14th, 2005, 04:09
  5. Excel VBA - Setting Print Area in VB
    By spikel in forum EXCEL HELP
    Replies: 4
    Last Post: April 24th, 2004, 00:19

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno