print from cell reference

kavanagh

Board Regular
Joined
Apr 1, 2009
Messages
155
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this: right click the dates-08-2-xls tab, select View Code and paste in

Code:
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

Board Regular
Joined
Apr 1, 2009
Messages
155
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

Legend
Joined
Jun 19, 2002
Messages
63,650
It should print. Hopefully this will stop it from repeating

Code:
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

Board Regular
Joined
Apr 1, 2009
Messages
155

ADVERTISEMENT

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

Board Regular
Joined
Apr 1, 2009
Messages
155
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

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Try this

Code:
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

Board Regular
Joined
Apr 1, 2009
Messages
155
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

Board Regular
Joined
Apr 1, 2009
Messages
155
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,539
Messages
5,596,754
Members
414,097
Latest member
FaeFen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top