Creating a print button using VB

[JonKeane]

Hi all. I have been given a small task which will require me to use VB code within an excel workbook.

I have been given a workbook that contains say 10 sheets. The first sheet is a summary sheet that will contain a 'Print' button. The idea is that when the Print button is selected all the sheets that's have been filled out will be printed.

For example.

I have a workbook that has 10 sheets to fill out. The user has filled out 7 out of the 10 sheets and when selecting the Print button, I require only the 7 completed sheets to print.

I thought the best way to go about this was to look at 1 particular cell on each sheet. If the cell is blank, do not print the sheet, otherwise do so.

I'm no VB coder by a long shot, although with a little help I can normally manage to make this kind of thing.

Any help this this would be most appreciated.

Thanks is advance for any help with this.

Jon Keane

 

[WinteE]

Jon,

Move the code to the 'ThisWorkbook' object.

Erik

 

[JonKeane]

WinteE, I have moved the code into 'This Workbook', although I now get 0 errors, nothing appears to happen.
I have uploaded a new version, if you have time would you take a look.

http://www.hillsdata.co.uk/public/jonkeane/vo.xls

Thankyou.

 

[WinteE]

Jon,

You can loop trough the code line by line when you first mark the For Each .. line by clicking on the grey bar in front of the line. When you start the macro the VBA code will appear and with key F8 you can step through the code.

Moving your cursor on e.g. vProjectNumber will show you it's contents.

As I don't have a printer up here I can't test this for you .

Succes, and please keep me in touch.

Erik

 

[JonKeane]

WinteE, the content of the variant are always empty even though I have set a named ranged to cell B2. For some reason it is not populating the vProjectNumber with any data. Any ideas?

 

[WinteE]

Jon,

Move the following code to Module1 :

Public Sub PrintSheets()

Dim vProjectNumber As Variant
vProjectNumber = Range("ProjectNumber").Value

    For Each WS In ActiveWorkbook.Worksheets
        If WS.Range("C12") = vProjectNumber Then
            WS.Select
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
        End If
    Next WS
    
End Sub

.. and copy

Private Sub PrintActiveSheets_Click()

    PrintSheets
    
End Sub

this code as code for your PrintActiveSheet button.

Remove all other code !

Erik

 

[Norie]

Jon

Try this.

Sub PrintSheets()
Dim ws As Worksheets
     
     For Each ws In Worksheets
            If ws.Range("C12")<>"" And IsNumeric(ws.Name) Then
                  ws.Printout
            EndIf
    Next ws
End Sub

 

[JonKeane]

WinteE, got it working now to an extent. Thanks for all you help with this issue. Only one or two other question, firstly how to I specify sheets to print, for example I want to print the basedata sheet everytime, how do I hard code that into the click event. Secondly, how do I tell it what printer to print to? At the moment it print to my default, bbut some user may wish to use a pdf writer or other printer installed on there machine.

Thanks, Jon

 

[JonKeane]

Can anybody point me in the direction of selecting what printer to use and how to print sheets by the sheet name.

Thanks

 

[marcsiddiqui]

I have a file which consists a sheet having 800+ rows data and in a summary sheet, I want 8 print buttons to print 100 summaries on every button.
it is a student result file hence I want to create this kind of button can u please help me.

 

[marcsiddiqui]

print button for 1st 100 drop down summaries.

I have a file consisting a sheet which has 800+ row and in a summary sheet I have created a drop-down list of all those rows but I don’t wanna print all those 800+ summaries at once, but in 8 parts of 100 each by using 8 print button.
Can you please tell me how to use VBA for it?