VB Code/Macro Printing with Page End pulled from Workbook

[S1L1SC]

This has been driving me up the wall all morning.

I have a worksheet that changes in length. There is a formula that calculates how many pages the sheet will be.

The also is a print button on the sheet that will print a coversheet "Sheets("Title")" and then it is supposed to print the data on the second sheet.

Below the code I have so far... I know I am missing something, but knowing very little about macros I just can't seem to figure it out.

Private Sub CommandButton1_Click()
    Application.Dialogs(xlDialogPrinterSetup).Show
    Application.ScreenUpdating = False
    Sheets("Title").Select
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Report").Select
        .End = ActiveSheet.Range("B3").Value
        ActiveWindow.SelectedSheets.PrintOut From:=1, To:=.End
End Sub

[se1429]

Hi here is a general syntax for specifying the print area:

Using entire row address:

ActiveSheet.PageSetup.PrintArea = "$1:$23"

Cell address

ActiveSheet.PageSetup.PrintArea = "$A$1:$E$19"

So if the start of your print area is "A1" for example, and you have the address of the last cell saved in "B3", then hopefully this macro will setup your print area:

Option Explicit
Sub SetPrintArea()
Dim sPrintArea As String
 ActiveSheet.PageSetup.PrintArea = ""
 sPrintArea = "A1:" & ActiveSheet.Range("B3").Value
 ActiveSheet.PageSetup.PrintArea = sPrintArea
End Sub

Related link:
http://www.exceldigest.com/myblog/20...f-a-worksheet/

Regards.

[S1L1SC]

Not sure if that would work.

The sheet I am trying to print has the page breaks pre-set so that the formating stays intact. All I really need to do is figure out how to pass the total number of pages (in this case the page number of the last page) to that function, so that it will print only pages 1-6 instead of the whole print (~96 pages) area.

I know something is wrong with the way I am trying to pass the number to the function, but I can't figure it out.

Please close - Figured out why it was not working.
Working Code below:

Private Sub CommandButton1_Click()
    Application.Dialogs(xlDialogPrinterSetup).Show
    Application.ScreenUpdating = False
    Sheets("Title").Select
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Report").Select
        ActiveWindow.SelectedSheets.PrintOut From:=1, To:=Range("B3").Value
End Sub