To run the macro, I suggest you add a button to the "Reports" worksheet.
Select the "Reports" worksheet.
From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)
In Developer - Controls select Insert and choose the button icon.
Draw the button on the "Reports" worksheet - but draw it outside the print area, i.e., outside A1:K75
In the 'Assign Macro' dialog box select 'New'
In the code window that opens enter this:
Private Sub Button1_Click()
Dim n As Integer
'set print area and print parameters
.PrintArea = "A1:K75"
.Orientation = xlPortrait
.LeftFooter = "Printed on: &D"
.LeftMargin = Application.InchesToPoints(0.590551181102362)
.RightMargin = Application.InchesToPoints(0.590551181102362)
.TopMargin = Application.InchesToPoints(0.393700787401575)
.BottomMargin = Application.InchesToPoints(0.393700787401575)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.CenterHorizontally = True
.CenterVertically = False
.Draft = False
.PaperSize = xlPaperLetter
.FitToPagesWide = 1
.FitToPagesTall = 1
'loop through all records 1 to 650
For n = 1 To 650
'change value in L9 drop-down
.Range("L9") = n
'print each record on named printer
Note that Sub Button1_Click() and End Sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().
Note that after 'With PageSetup' line there are a series of features that you can set as required. (I did not include some of the default settings). In particular you can set the page size and orientation and force the report to fit on to one page.
I included the 'Printed on:' date in the left footer, just as an example of what you can do.
(.LeftFooter = "Printed on: &D")
Page margins can also be set.
Click Save from the Visual Basic Menu.
Then hit Alt+f11 (The Alt key and Function key 11 pressed together) to take you back to the main Excel window.
Right click the button and Edit the name to something meaningful, such as "Print all records"
As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'
In Excel select Print and select the printer to be used, then Cancel the print dialog box. This sets the printer to be used by the macro. Alternatively you can change the line:
to include the printer name, e.g.,
.PrintOut ActivePrinter:="My Printer"
After selecting any cell, the new command button should now respond to a click and run the macro.
The macro will select and print all 650 records.
To test the macro, you may want to limit the number of records printed - just change the 'For n =' line to:
For n = 1 to 3 instead of For n = 1 to 650
and you will get the first three records printed.
The fact that you are taking data from two other worksheets does not matter.
This macro just does what you did manually - changes the number in cell L9 on the "Reports" page and then prints the range A1:K75" and then does this 650 times.