Results 1 to 8 of 8

Print one row per page for range of rows

This is a discussion on Print one row per page for range of rows within the Excel Questions forums, part of the Question Forums category; I am new to VBA . I have my grade sheet in excel: assignments across the top in columns A ...

  1. #1
    New Member
    Join Date
    Dec 2011
    Location
    Elkhart, Indiana
    Posts
    4

    Default Print one row per page for range of rows

    I am new to VBA.

    I have my grade sheet in excel: assignments across the top in columns A - Z, and student names and grades in rows from row 6 to row 106.

    I would like to be able to select a range of rows (one of four class sections) and print one row for each student name on a individual page (landscape).

    Last year I would set rows 1-5 as titles, and then manually select row 6, Print...
    With 100 students, this gets a little boring.

    How can I use VBA to set the print range to increment through a range of rows one at a time?
    ActiveSheet.PageSetup.PrintArea = ? ? ? ?
    ActiveSheet.PrintOut

  2. #2
    Board Regular
    Join Date
    Nov 2011
    Posts
    1,062

    Default Re: Print one row per page for range of rows

    I hope this works for you. I didn't test printing, but PrintPreview worked

    1. Set PrintArea
    2. Hide all the rows with data

    For each row of data:
    3. "Unhide" the row to be printed
    4. Print
    5. "ReHide" printed row
    next row

    Finally
    "Unhide" all rows

    Code:
    Sub printrow()
        ActiveSheet.PageSetup.PrintArea = "$1:$106"
        Rows("6:106").EntireRow.Hidden = True
        For i = 6 To 106
            Rows(i).EntireRow.Hidden = False
            'ActiveWindow.SelectedSheets.PrintPreview
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
            Rows(i).EntireRow.Hidden = True
        Next i
         Rows("6:106").EntireRow.Hidden = False
    End Sub

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,582

    Default Re: Print one row per page for range of rows

    Jim

    Welcome to the MrExcel board!

    Try this. Test with very small sections to start with if you are actually using paper.
    Better if you can set to print to a pdf printer if you can for testing.

    1. Set your 'Rows to Repeat at Top' manually in Page Setup as usual (say rows 1-5).

    2. Select the group of rows you want to print (including disjoint rows if you want). This is just the student rows, not the heading rows.

    3. Run the macro below.


    Sub PrintSelectionOneLinePerPage()
        Dim c As Range
        
        ActiveSheet.ResetAllPageBreaks
        For Each c In Intersect(Selection.EntireRow, Columns("A"))
            ActiveSheet.HPageBreaks.Add c.Offset(1)
        Next c
        Selection.PrintOut
        ActiveSheet.ResetAllPageBreaks
    End Sub

    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  4. #4
    New Member
    Join Date
    Dec 2011
    Location
    Elkhart, Indiana
    Posts
    4

    Default Re: Print one row per page for range of rows

    Peter,
    What an elegant solution!
    I have wrestled with this for about a week. This opens up new techniques for me; and I have taught spreadsheets since 1983 (VisaCalc).
    Thanks.

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,582

    Default Re: Print one row per page for range of rows

    Quote Originally Posted by JimChampaigne View Post
    Peter,
    What an elegant solution!
    I have wrestled with this for about a week. This opens up new techniques for me; and I have taught spreadsheets since 1983 (VisaCalc).
    Thanks.
    Jim

    Thanks for that, glad to help.

    I assume you do sometimes wish to print the whole lot together rather than one line per page, but if not you could of couse just add a page break after every student then you wouldn't even need the macro.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  6. #6
    New Member
    Join Date
    Dec 2011
    Location
    Elkhart, Indiana
    Posts
    4

    Default Re: Print one row per page for range of rows

    Peter,
    Just a followup---

    The code you sent works perfectly in a new workbook.
    However, when I add the code to my existing grade sheet (using Office 2010 64 bit), I get slightly different results.

    1) When I select row 7 and then shift click to select row 10 and run the Macro, All 4 rows show in one print page.

    2) When I select row 7, then Ctrl click row 8, Ctrl click row 9 and then 10, the program works as advertised. One record per page for each student.

    To perform a quick autopsy, I recorded the selection macros in both spreadsheets.
    (for option 1 above)
    New Spreadsheet ...Rows("7:10").Select

    Old Gradesheet ... ActiveCell.Rows("1:4").EntireRow.Select


    (For option 2 above)
    New Spreadsheet: Range("A1,6:6,7:7,8:8,9:9,10:10").Select
    Range("A10").Activate

    Old Gradesheet: ActiveCell.Range("A1,7:7,8:8,9:9,10:10").Select
    ActiveCell.Offset(9, 0).Range("A1").Activate

    The Gradesheet may have been created in Office 2007 ( I have taught at Indiana University for past 12 years) I have saved it as a .XLSM file.

    Do I need to create a new gradesheet, or is there a setting that would create the desired result?

  7. #7
    New Member
    Join Date
    Dec 2011
    Location
    Elkhart, Indiana
    Posts
    4

    Default Re: Print one row per page for range of rows

    Solution Found!

    I had the Print Settings in my grade sheet set to Fit to 1 page wide by 1 tall.

    When I chose Adjust to: nnn% the macro works correctly.

    As you can see, I am still in discovery mode.
    Thanks again for your assistance.

    Jim Champaigne

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,582

    Default Re: Print one row per page for range of rows

    I'm glad you found that, I'm not sure I would have thought of it.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com