Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel XP - Dynamic Print Range (XP)

    Could some one please supply a piece of VB code? I would like to reset the print area on Calculate. This to be done for both columns and rows. i.e. I would like the print area to automatically adjust each time my pivot table refreshes. The pivot has page items which produce a different number of rows and columns each time the page item is changed and it would be really useful not to have to reset the print area on each occasion.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel XP - Dynamic Print Range (XP)

    If no print area is set, Excel automatically adjusts the area to print according to the used range of a worksheet.

    So this oneliner should do the trick:

    Activesheet.Names("Print_Area").Delete

    Put that code into the code module behind the sheet, in e.g. the sheet_Activate event.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel XP - Dynamic Print Range (XP)

    Sorry. This did not work because I need to be able to flick between several page items on the pivot and it would seem that once the first print area is set Excel will not change this. I have tried altering the code module to a sheet_Calculate event, but still no joy. I do have a piece of code that I use to adust the length of rows within a print area, but it depends on the number of columns being static and I need the columns to automatically adjust as well.

    Code is:

    Private Sub Worksheet_Calculate()

    Dim sRange As String
    Dim iStartCol As Integer
    Dim iNumCols As Integer

    iStartCol = 5 'Start column number
    iNumCols = 30 'number of columns to include

    sRange = Range(Cells(1, iStartCol), _
    Cells(65536, iStartCol).End(xlUp)). _
    Resize(, iNumCols).Address
    ActiveSheet.PageSetup.PrintArea = sRange

    End Sub

    All help much appreciated.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel XP - Dynamic Print Range (XP)

    The point I tried to make is NOT to set a print area in the first place.
    Or have I misunderstood and do you actually need a set printarea?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel XP - Dynamic Print Range (XP)

    Hi. Apologies. I don't think I'm explaining this too well. My pivot table (and the other items I need to print on the same worksheet) need to be scaled to fit on A4 landscape after user updates the page item, so perhaps I do need something that does set/reset print areas.

    Many thanks

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel XP - Dynamic Print Range (XP)

    If you are printing everythig on the sheet, you do not need to set the print range before hand, excel will just print everything on the sheet as an "implicit" print range.

    If you are not printing everything on the sheet, and want an explicit print range based on a changing "pivot table", you could use something like this:

    ActiveSheet.PageSetup.PrintArea = _
    ActiveSheet.PivotTables(1).TableRange2.Address

    The tablerange2 property is the range including the page fields. The tablerange1 includes just the privot table "proper". There are other ranges, if you need other ranges.

    Steve

  7. #7
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel XP - Dynamic Print Range (XP)

    Hi Chaps. Thanks for all your help on this, but I'm now way out of my depth and don't want to waste any more your time. So I'm abandoning this and will work on a less ambitious way of getting the job done.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel XP - Dynamic Print Range (XP)

    You don't have to give up on our account, especially if your question still needs to be answered.

    Perhaps if you detailed more what you had and what you want, we might better be able to answer it. To be honest I am not completely clear on your setup and what the "problem" is completely.

    Steve

Posting Permissions

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