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.


    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
    ActiveWindow.SmallScroll Down:=-36
    End Sub


    Thanks in advance,

  • Hi scy


  • 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?


  • 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?


  • 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

    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.



Participate now!

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