Print one row per page for range of rows

JimChampaigne

New Member
Joined
Dec 30, 2011
Messages
4
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> PrintSelectionOneLinePerPage()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    ActiveSheet.ResetAllPageBreaks<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Intersect(Selection.EntireRow, Columns("A"))<br>        ActiveSheet.HPageBreaks.Add c.Offset(1)<br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    Selection.PrintOut<br>    ActiveSheet.ResetAllPageBreaks<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
I'm glad you found that, I'm not sure I would have thought of it. :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top