Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print what I see! (XL 2000)

    I have a spreadsheet that I am trying to set up as a template, and am freezing the pane so that as time goes on, users can scroll to the right along a time-line (each column is a month's data) and not have to see all of the old data. Is there a way to print just what is currently frozen (Column A and Rows 1&2) on the screen plus the next 6 columns?

    Thank you in advance!
    Don Liebman
    San Diego, CA 92115

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print what I see! (XL 2000)

    I'm not sure I understand what you want. You can specify that the "frozen" rows and columns are printed along the top and left edge of each page:
    - Select File | Page Setup...
    - Activate the Sheet tab.
    - Click in the 'Rows to repeat at top' box and point at or type the rows you want to repeat, in your situation $1:$2.
    - Click in the 'Columns to repeat at left' box and point at or type the columns you want to repeat, in your situation $A:$A.
    - Click OK.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Print what I see! (XL 2000)

    Hans,

    Don has a spreadsheet that has rows/columns frozen. He has then scrolled down and across to view details on the sheet under the frozen headings. Since the headings are frozen, some rows get hidden when you scroll down the sheet. If you preview the sheet, the "hidden" rows are visible as the are not really hidden. Don does not want these rows to print. He wants a printout of just the data as seen on screen after he has scrolled down. Almost like a Copy Picture scenario.
    Regards,
    Rudi

  4. #4
    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: Print what I see! (XL 2000)

    As mentioned before You can set Col A and rows 1/2 to print on every sheet using page setup
    file -page setup - sheet tab -
    rows to repeat at top:$1:$2
    cols to repeat at left: $A:$A

    You can also set the range to print in the print area. M25:R100 (or whatever) and the area will print with rows 1/2 and col A.

    If you want to automatically set the print area to a range you can do it with a before print macro, but it is not clear to me what you mean by "next 6 columns". Are you referring to the last 6 columns of data or something else? If the last column of the sheet you could use something like this

    <pre>Option Explicit
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim iFirstCol As Integer
    Dim iLastCol As Integer
    Dim lLastRow As Long
    Dim lFirstRow As Long
    Dim rng As Range
    With Worksheets(1) 'Change as desired
    .PageSetup.PrintTitleRows = "$1:$2"
    .PageSetup.PrintTitleColumns = "$A:$A"
    Set rng = .Cells.SpecialCells(xlCellTypeLastCell)
    lFirstRow = 3
    iLastCol = rng.Column
    lLastRow = rng.Row
    iFirstCol = iLastCol - 5
    If iFirstCol < 2 Then iFirstCol = 2
    Set rng = .Range(.Cells(lFirstRow, iFirstCol), _
    .Cells(lLastRow, iLastCol))
    .PageSetup.PrintArea = rng.Address
    End With
    Set rng = Nothing
    End Sub</pre>


    The code goes in the thisworkbook object. Change the sheetname you want to do this for in the code as appropriate.

    If you want to print the visible range, you can adapt something like this:
    <pre>Option Explicit
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    if activesheet.name = "Sheet1" 'Change as desired
    With Activesheet
    .PageSetup.PrintTitleRows = "$1:$2"
    .PageSetup.PrintTitleColumns = "$A:$A"
    .PageSetup.PrintArea = intersect(Activewindow.visiblerange, _
    .range(.cells.specialcells(xlcelltypelastcell), _
    .range("a1"))).address
    End With
    end If
    End Sub</pre>


    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Print what I see! (XL 2000)

    Thanx for that code Steve. I picked up another valuable tip from it that I can use in other code I have. <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>
    Regards,
    Rudi

  6. #6
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print what I see! (XL 2000)

    What I should have clarified is that I only want to print 6 columns of data to the right of the frozen area. I have modified the XL spreadsheet to freeze columns A, B, and C (see attachment). When I try printing the XL sheet, it always prints the entire range that I have set the print area to, regardless of what I am viewing on the screen. I really want to print the frozen columns and rows + the next six columns of data that are currently viewed.

    I will take a look at using the code you posted, as it looks like the direction I am looking for.

    Thank you!
    Don Liebman
    San Diego, CA 92115

  7. #7
    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: Print what I see! (XL 2000)

    Either code I wrote could be adapted. Just change the line to:

    PrintTitleColumns = "$A:$C"

    The first just sets up the last 6 columns of data to print. The second prints the visible area. If you need something else you will have to be a little more specific...

    Steve

  8. #8
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print what I see! (XL 2000)

    Steve,

    Is there a way to determine what the first column to the right of Column C is, and then set the print area to show "$A:$C" + "$(1st column to the right of C):$(6th column to the right of C)" using the techniques you posted previously, or another method? That is what my preferred goal would be. Otherwise, what you supplied above will work, I just have to manually adjust the screen window size to the area I want to print, then the "visible area" code works well.

    Thanks!
    Don Liebman
    San Diego, CA 92115

  9. #9
    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: Print what I see! (XL 2000)

    The first column to the right of C is always D <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    The second code I list shows how to determine the visible range (is this what you mean - the first visible column past C). This range does not include the "frozen panes". It could be modified to get the 6 cols past the first visible column:

    <pre>Option Explicit
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim rng As Range

    If ActiveSheet.Name = "Sheet1" Then 'Change as desired
    With ActiveSheet
    Set rng = Intersect(ActiveWindow.VisibleRange, _
    .Range(.Cells.SpecialCells(xlCellTypeLastCell), _
    .Range("A1")))
    Set rng = rng.Resize(, 6)
    .PageSetup.PrintTitleRows = "$1:$2"
    .PageSetup.PrintTitleColumns = "$A:$C"
    .PageSetup.PrintArea = rng.Address
    End With
    End If
    Set rng = Nothing
    End Sub</pre>


    Steve

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print what I see! (XL 2000)

    The two statements below will give you the column number of the first and last column in the repeated column area:

    Dim lFirstTColNum As Long, lLastTColNum As Long
    lFirstTColNum = Range(ActiveSheet.PageSetup.PrintTitleColumns).Col umn
    lLastTColNum = Range(ActiveSheet.PageSetup.PrintTitleColumns).Col umns.Count + Range(ActiveSheet.PageSetup.PrintTitleColumns).Col umn - 1
    Legare Coleman

  11. #11
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print what I see! (XL 2000)

    Steve,

    Yes, I was referring to the first visible column to the right of C. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Thanks for all your help (and Rudi's and Legare's)!
    Don Liebman
    San Diego, CA 92115

  12. #12
    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: Print what I see! (XL 2000)

    Just making sure:
    Does the last code I posted do what you need to have done?

    Steve

  13. #13
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print what I see! (XL 2000)

    Steve,

    It is close enough that I can tinker with it to complete the coding. I will post again if I have any questions.

    Thank you!
    Don Liebman
    San Diego, CA 92115

Posting Permissions

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