print from cell reference

[kavanagh]

I'm trying to make a worksheet print automatically.

When the cell E10 of worksheet dates-08-2-xls hits >=186 days
the other workbook diva.xls worksheet 15 A1. prints

how would i code this, any help would be greatly appreaciated

thank you k

 

[VoG]

Try this: right click the dates-08-2-xls tab, select View Code and paste in

Private Sub Worksheet_Calculate()
If Range("E10").Value >= 186 Then
    Workbooks.Open filname:="Diva.xls"
    Sheets(15).PrintOut
    ActiveWorkbook.Close False
End If
End Sub

This assumes that E10 contains a formula and Diva.xls is initially closed.

 

[kavanagh]

Thank you for responding
I tried the code and it goes to div a sheet ok but it just keeps opening the sheet ,returning to dates then opening again , repeating this procedure over and over until the cows come home
not wanting to print at all.

any tweeks thank you k

 

[VoG]

It should print. Hopefully this will stop it from repeating

Private Sub Worksheet_Calculate()
If Range("E10").Value >= 186 Then
    Application.EnableEvents = False
    Workbooks.Open Filename:="Diva.xls"
    Sheets(15).PrintOut
    ActiveWorkbook.Close False
    Application.EnableEvents = True
End If
End Sub

 

[kavanagh]

Thank you but it dosen't want to do any thing now, it doen't go to the sheet to be printed, nothing happens I tried tweeking it but to no avail
any other ideas appreaciated. thank you k

 

[kavanagh]

Hey it is working ,I didn't realize iI had to close the workbook then when I reopened it works.Thank you
one question if I wanted it to react to multiple cells such as e 10 and e 52 e 94 do I need to redo the formula for each or is there an easier way.

thanks again k

 

[VoG]

Try this

Private Sub Worksheet_Calculate()
Dim c As Range
For Each c In Range("E10,E52,E94")
    If c.Value >= 186 Then
        Application.EnableEvents = False
        Workbooks.Open Filename:="Diva.xls"
        Sheets(15).PrintOut
        ActiveWorkbook.Close False
        Exit For
    End If
Next c
Application.EnableEvents = True
End Sub

 

[kavanagh]

Thank you but

that cell e52 would have to print sheet 16 on div a .xls

and e94 would have to print sheet 8 div a .xls

and so on for different units,I tried to copy and alter the numbers for the next set such as

Private Sub Worksheet_Calculate()
If Range("E52").Value >= 186 Then
Application.EnableEvents = False
Workbooks.Open Filename:="Div A.xls"
Sheets(16).PrintOut
ActiveWorkbook.Close False
Application.EnableEvents = True
End If

but I get an ambiguous name warning .
is this the way to go or is there an easier way

appreaciate the help K

 

[VoG]

Try

Private Sub Worksheet_Calculate()
If Range("E10").Value >= 186 Then
    Application.EnableEvents = False
    Workbooks.Open Filename:="Diva.xls"
    Sheets(15).PrintOut
    ActiveWorkbook.Close False
    Application.EnableEvents = True
ElseIf Range("E52").Value >= 186 Then
    Application.EnableEvents = False
    Workbooks.Open Filename:="Diva.xls"
    Sheets(16).PrintOut
    ActiveWorkbook.Close False
    Application.EnableEvents = True
ElseIf Range("E94").Value >= 186 Then
    Application.EnableEvents = False
    Workbooks.Open Filename:="Diva.xls"
    Sheets(8).PrintOut
    ActiveWorkbook.Close False
    Application.EnableEvents = True
End If
End Sub

 

[kavanagh]

Thank you that works great,
only caviate is the if there is more than one unit coming due at the same time such as E10 and E52 it will only print out the first one instead of both. I don't know if there is a fix for that. but that dosen't happen too often anyway.

Thank you again for the code it makes my life a lot easier K