Announcement

Collapse
No announcement yet.

Setting print area to pivot table?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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?


  • #2
    Re: Setting print area to pivot table?

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

    Code:
    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, 17:46.
    Kind Regards, Will Riley

    LinkedIn: Will Riley

    Comment


    • #3
      Re: Setting print area to pivot table?

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

      Comment


      • #4
        Re: Setting print area to pivot table?

        code in use, (Called from Userform)

        Code:
        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

        Comment


        • #5
          Re: Setting print area to pivot table?

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

          LinkedIn: Will Riley

          Comment


          • #6
            Re: Setting print area to pivot table?

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

            Code:
            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!

            Comment


            • #7
              Re: Setting print area to pivot table?

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

              LinkedIn: Will Riley

              Comment

              Working...
              X