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]

Turn on the macrorecorder (Tools, Macro, Record macro) and start printing the sheets you want to. Insert a button and connect the recorded macro to it.

 

[JonKeane]

WinteE, thankyou for the quick reply.

I have already tried going down the macro route, but this doesnt work how i need it too. Perhaps i didnt explain my origianl excaample very well, i will try to again but with a little more details.

The workbook I am designing will be for engineers to download and fill out whilst out on site. Not all jobs will require all the sheets to be filled out and printed. What I need to do is create a button that will only print the sheets the engineer has had to fill out for his job. The workbook may have 50+ sheets when completed, and if an engineers job only required them to fill out the first 10 sheets, there is no point in the other 40 sheets printing off too. Each sheet with require the job number to be completed in cell 1A, so my idea was to only print sheets that have cell 1A populated with data.

Hope that will give you a better idea of what I trying to do.

Thanks again.

Jon

 

[WinteE]

Hi Jon,

You can use the following code to go through all worksheets in yhe active workbook :

Sub PrintWorksheets()

Dim vProjectNumber as Variant

vProjectNumber = Range("ProjectNumber").Value

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

End Sub

Make the field where the main project number will be entered a named range "ProjectNumber". This will make it possible to start printing from whatever sheet you're on.

Succes,
Erik

 

[JonKeane]

WinteE, again thankyou for the speedy reply.

Will be looking at this again later today, so will post back with how i got on later.

Cheers

Jon

 

[JonKeane]

Have tried what you suggested but unfortunatly it hasnt worked in they way it needs to for my workbook. Although would work great for another application i have..

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

The above link points to a copy of the workbook in question.

If you look at the first sheet you will see i have created the print button. There are then various sheets that will be required to print. You then will see I have 10 sheets numbered 1 -10. These are the sheets that I only want to print if they have a value (job number) in cell B17.

Also need it to prompt for which printer to use.

Thanks for any further help with this.

Jon

 

[WinteE]

Make cell B2 on sheet Basedata a named range (Insert, Name, Define) 'ProjectNumber'.

Sub PrintWorksheets()

Dim vProjectNumber as Variant

vProjectNumber = Range("ProjectNumber").Value

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

End Sub

As in sheet 1 cell B12 refers to Database!B2 this page will be printed, just as every other page where cell B12 matches Database!B2.

I don't know if it's possible to select a printer. It probably will, maybe someone else can help.

Erik

 

[JonKeane]

WinteE,

Have set cell B2 on basedata to be a Range named ProjectNumber, but when I run the code I get a run time error 1004. Method, Range of object worksheet failed. Any ideas??

 

[WinteE]

Try this :

Sub PrintWorksheets()

Dim vProjectNumber as Variant

vProjectNumber = Range("ProjectNumber").Value

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

End Sub

 

[JonKeane]

WinteE, sorry to be a pain, but I am still getting errors. It appears that the variant is not getting passed any data fomr the basedata sheet.

I have re-uploaded the .xls with the latest code. If you dont mid, would you be able to take a look at the workbook and possibly advise on where I am going wrong.

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

Your help with this is very much appreciated.

Jon