Excel Guru's required

[Daff_Duck]

I have a workbook which on one sheet I input data and it has a formula which creates another set of data on a second sheet.

The question I have is, each sheet needs to be printed on to different sizes of paper the first A4 and the second Label. I have a location on one sheet and a verification on the second Is it possible to set up so that this can be done without the need to change the paper source each time I want to print.

Could it be done by making a copy of the default printer and making one print the first sheet and the second print the other? The only thing is this is the same workbook and not separate the formula has to go into the work book and output to the second sheet so that verification codes can be produced. and printed

If anyone could tell me IF it can be done and if so how? Many Thanks

 

[Phonesis]

Hi Duck,

You can achieve this via VBA. Are you familiar with VBA coding in Excel?

If so, add this as a macro:

Sub printing()
    For i = 1 To ActiveWorkbook.Worksheets.Count
     Worksheets(i).Activate
        With Worksheets(i).PageSetup
             If i = 2 Then
                .PaperSize = xlPaperA4 'change this value to the size you need
             Else
                .PaperSize = xlPaperA4
            End If
        End With
     ActiveSheet.PrintOut
    Next i
End Sub

I am not sure what you mean by Label page size (it isn't listed here) so you will need to find the right option and alter the line I have commented.

Hope this helps.

 

[andshrew]

I'm not sure setting the paper size will help here as presumably these are avery type labels on A4 sized paper? If my assumption is correct then what you really need is a way to specify which paper tray you want it to print from which as far as I know isn't possible from Excel VBA.

So it's messy, but the simplest solution may be as you've suggested. Install your printer twice giving them unique names. Set one to default to A4, and the other to the tray with your labels. You can then create a macro using the ActiveSheet.PrintOut command as you can specify within this the name of the printer you would use. You could modify the above code to achieve this.