Printing another sheet without changing view

    Probably a quick fix question, but does anyone know how to make this code work without making the view switch to the 103Bouts sheet? I want all the information to be applied to that sheet, and to print that sheet, without the user seeing the actual sheet. Right now the view switches to that sheet, does its configuration, and then switches back to the 103 page.


    <Code&gt;


    Sheets("103Bouts").Select
    Range("A1:I80").Select
    ActiveSheet.PageSetup.PrintArea = "$A$1:$I$80"
    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$I$80"
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlPortrait
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 91
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("103").Select
    ActiveWindow.SmallScroll Down:=-36
    Range("A1:N1").Select
    End Sub


    </Code&gt;


    Thanks in advance,
    -Scy

    Hi scy


    Try



    Got to thinking.. I need this macro to run for 12 different sheets. My question is this:


    Can the macro be set up so that it selects the next sheet, even if it is hidden, applies the appropriate settings, and prints the sheet, like the current code does, but makes it universal, so any page can use it?


    Thanks,
    -Scy

    You could modify Dave's macro to make it a callable sub as follows:


    Then in your macro for the 12 sheets jsut call this sub 12 times. For the 103Bouts the call would be:
    SpecialPrint "103Bouts", ""$A$1:$I$80" , 91

    Derk-


    I follow what you are saying, and I have copied the code over, however, I guess I'm not sure how to make the calls happen. I assume that I just need to make another macro for each button, but that is what I wanted to avoid.


    I have the sheets set up with buttons that are presesd to print the bout sheets, is it possible to link procedure call to a button for easy use?


    Thanks,
    -Scy

    Yes you will need a macro for each button (it could be the same macro that took different action depending on which button called it). The simples would be to have a one line macro for each button that called then called the main macro. That way, if you ned to make a change to the priint setup there is just one place to change. The one line macro for 103Bouts would be:
    Sub Something
    SpecialPrint "103Bouts", ""$A$1:$I$80" , 91
    end sub


    Or if all sheets had the same print area and zoom factor and you wanted them all pronted from one button you could use

    Code
    Sub DoAll()
    Dim w As Worksheet
    For Each w In ActiveWorkbook
        SpecialPrint w.Name, "$A$1:$I$80", 91
    Next w
End Sub


    It all depends on what you want to do.

    Ok, got it.


    One more thing though. :biggrin:


    Is it instead possible to tell it to select the next sheet, even if it is hidden, and run the macro? I prefer to use one macro that can be used on each sheet. So the button is pressed, the macro to set the print area, etc. is run on the next sheet in order. That would save space and time of creating a new macro each time, if it is possible that is.


    Thanks.


    -Scy

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login