Printing workbook

[mjbanderson]

I may regret trying to do this myself, but I dont know til I try...

First off, I dont know VBA code, but there are some people here who do, so this may turn into a reference thread for them to accomplish this, unless I can somehow manage to get it done with help from forum members who are waaay smarter than me.

I asked our I.T department for a macro button in Excel to print the workbook(just a worksheet would be ok too) while always skipping over the first sheet. It can't leave any residual formatting behind after closing the file because some people here still need to print it out.

It's kind of a PITA to set the print preference to Pages 2-3 for every workbook I print out of, but that's what I currently have to do(or print out the unwanted sheet and then throw it away).

Any help would be appreciated, but understand if you want to pass on this one!

 

[mole999]

what paper size and orientation

what is the sheet you skip over called

how many pages wide / deep

what column / row are involved, are they always the same or always vary

 

[mjbanderson]

what paper size and orientation

8-1/2 x 11, landscape (always)

what is the sheet you skip over called

The sheet isnt always named the thing, so it could change from file to file.

how many pages wide / deep

It's always one page wide, and usually 2 pages deep, but sometimes more.

what column / row are involved, are they always the same or always vary

Columns are always A through P.
Rows always vary.

 

[mole999]

do the sheets you need to print have regular names ?

If the first sheet is different how different

are they always new workbooks, which might mean the default first page could be "sheet1"

 

[mjbanderson]

Generally speaking, they do have 'regular' names. Usually, the first sheet is called 'Op. 200', second sheet is called 'Op. 300', and so on...
Explaining where I'm comping from may help out too:

I work in a machine shop and we use Excel for setup sheet for the guys on the floor.
Page 1 is used for describing the tools. Sometimes there are 5 tools, sometimes 20. The number of rows will always vary.
Page 2 is used for showing 'part specific' instructions, pictures, and other necessary information. The number of row is always the same, but what rows they are in is subject to how many tools are in Page 1.

If the part has 3 operations to make the part, there are 3 sheets that contain the above information.
(I saved pictures to insert in this post, but they allow me to insert them from my computer).

Did I word that out well enough to visualize?

 

[mole999]

OK, what about your header on page 1, is it different from other pages, so much so that we could test for a word at the same location each time to make sure we can skip that sheet

 

[mjbanderson]

Unfortunately, the header(3 rows in total) on page#1 is the same as the other pages in that sheet.
However, there are words always on the 2nd page that never appear on the first(ex: 'Set-up Log' or 'Emp#').

 

[mole999]

are those words in the same place, in which case looking for them means we might be able to exclude the primary sheet

 

[mjbanderson]

They are in the same place in relation to the header of sheet#2, but since the tool numbers vary on sheet#1, it ends up being on different rows each time.

 

[mole999]

this would set the page for printing

Sub Macro1()
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 0
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
End Sub

I don't know the best way to proceed to set through each sheet, decide if the right words exist and then print only those sheets. It can be done, though i'm not as slick with the vba to make it great. once you have a working part it could be added to an xla file to give you 1 button control, beyond my basics