Printing Mutiple documents from a changing list

[JamesT1]

Hi Folk

Is it possible to have a macro to print documents that are showing in a list which is constantly changing

I have a list of job titles.... I have a list of 'Risk Assessments',,, some risk assessments cover some of the jobs but not others.

e.g.,,,,,,,,, "Driver", Risk assessments 1,4,7,8,9,.... "Production Operator" 1,4,7,10,12,15

Using a dropdown list, when I choose a job my list shows the relevant 'Risk Assessments'. I would like to have a button which would only print the relevant RA documents showing in the list

Many Thanks

[JBeaucaire]

If you had a sheet in your workbook that was a table...in one column these RA documents as identified in your drop downs (like 1,4,7,10,12,15?)....then the next column is the full path to the document you would want opened/printed...this layout would make it simple to construct a macro to lookup each document in this table and print it.

More than this would require much more from you. Best of all would be a sample document demonstrating all of this.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

[JamesT1]

Hi JBeaucaire

Thanks for your reply,,,on the attached book I have added a second sheet with the lists you mentioned... on selecting the jobs on the front sheet it will show the RA required for that job.... this is the list I would like the macro to print

many thanks

[JBeaucaire]

This should do it:

Option Explicit

Sub PrintAssessments()
'Jerry Beaucaire  5/30/2010
'Print a changing list of assessment workbooks
Dim RArng   As Range
Dim RA      As Range
Dim PATHS   As Range
Dim RApath  As String
Dim PrintMe As Workbook
Application.ScreenUpdating = False

Set RArng = Sheets("Required RA").Columns("C:C").SpecialCells(xlCellTypeFormulas, 2)
Set PATHS = Sheets("RA Paths").Range("A3:B" & Rows.Count).SpecialCells(xlCellTypeConstants)

    For Each RA In RArng
        RApath = Application.WorksheetFunction.VLookup(RA, PATHS, 2, 0)
        Set PrintMe = Workbooks.Open(RApath)
        ActiveSheet.PrintOut copies:=1
        PrintMe.Close False
    Next RA

Set RArng = Nothing
Set PATHS = Nothing
Application.ScreenUpdating = True
End Sub

If it doesn't work as expected, be sure to DEBUG and give me as much information as you can about what line of of code failed.

[JamesT1]

Hi JBeaucaire

Apologies haven't had the chance to test this until today......

there is an error code 1004....... Risk Assessment 2.xls... cannot be found

Set PrintMe = Workbooks.Open(RApath)

The risk assessments are word documents,,, could that be the problem

many thanks

[JBeaucaire]

Yes indeed, that's a huge omission of info... (nudge).

Try this instead:

Option Explicit

Sub PrintAssessments()
'Jerry Beaucaire  6/2/2010
'Print a changing list of assessment WORD documents
Dim RArng   As Range
Dim RA      As Range
Dim PATHS   As Range
Dim RApath  As String
Dim wrdApp  As Object
Application.ScreenUpdating = False

Set wrdApp = CreateObject("Word.Application")
Set RArng = Sheets("Required RA").Columns("C:C").SpecialCells(xlCellTypeFormulas, 2)
Set PATHS = Sheets("RA Paths").Range("A3:B" & Rows.Count).SpecialCells(xlCellTypeConstants)

    For Each RA In RArng
        RApath = Application.WorksheetFunction.VLookup(RA, PATHS, 2, 0)
        With wrdApp.documents.Open(RApath)
            .PrintOut Background:=False, Copies:=1
            .Close False
        End With
    Next RA
    
wrdApp.Quit False
Set wrdApp = Nothing
Set RArng = Nothing
Set PATHS = Nothing
Application.ScreenUpdating = True
End Sub

[JamesT1]

Hi JBeaucaire

this works a treat,,, thank you very much,,,,, appreciated,,

apologies for not mentioning the RA s are Word Docs,,, this is the first time I've tried anything like this and it didn't enter my mind,,,,,,,, i'll remember this for the future

Thank you

JT