Forum Discussion
Auto Print
I have the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim PrintRange As Range
Set KeyCells = Range("A2:A2")
Set PrintRange = Range("A1:B3") ' Modify this range as needed
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
If Target.Value <> "Not found" Then
PrintRange.Worksheet.PageSetup.PrintArea = PrintRange.Address
PrintRange.Worksheet.PrintOut
End If
End If
End Sub
The only problem it does not work because the content of cell A2 is a formula, it is =D2
So I am looking for whenever the content of D2 changes, print. And the content of D2 is another formula more complicated.
To restrict the code to a specific sheet, you can simply specify the sheet name in the code. Here's how you can modify the code to only run on a specific sheet:
Vba code is untested, please backup your file.
Private Sub Worksheet_Calculate() Dim KeyCell As Range Dim PrintRange As Range Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace "YourSheetName" with the name of your specific sheet Set KeyCell = ws.Range("A2") ' Cell A2 is being monitored Set PrintRange = ws.Range("A1:B3") ' Modify this range as needed If KeyCell.Value <> "Not found" Then PrintRange.Worksheet.PageSetup.PrintArea = PrintRange.Address PrintRange.Worksheet.PrintOut End If End SubReplace "YourSheetName" with the name of your specific sheet where you want the code to run.
Regarding the issue of not printing when the value is repeated, it seems like the code is set up to print only when the value in cell A2 changes. If you want it to print every time the value in cell A2 is not "Not found", regardless of whether it's repeated or not, you can remove the condition If KeyCell.Value <> "Not found" Then. Here's the modified code:
Vba code is untested, please backup your file.
Private Sub Worksheet_Calculate() Dim PrintRange As Range Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace "YourSheetName" with the name of your specific sheet Set PrintRange = ws.Range("A1:B3") ' Modify this range as needed PrintRange.Worksheet.PageSetup.PrintArea = PrintRange.Address PrintRange.Worksheet.PrintOut End SubThis will print the specified range every time the worksheet is calculated, regardless of the value in cell A2. Make sure to adjust the range and sheet name as needed for your specific scenario. The text, steps and the code were created with the help of AI.
If this is not what you want, please insert a demo file and explain step by step what you want.
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
7 Replies
Why don't you take the values from cell D2?
Just change the cell on that line of your code.
Set KeyCells = Range("D2")
Otherwise, I recommend adding detailed information about the formulas and cells.
It would also help if information about the Excel version, operating system, storage medium, file extension, etc. were available.
From D2 does not work either, if you manually enter the value works but if its a calculated cell it does not, this is the formula in D2=IF(ISNA(VLOOKUP(E2,VendorID!K:L,2,FALSE)),"Not found",VLOOKUP(E2,VendorID!K:L,2,FALSE))
Microsoft® Excel® for Microsoft 365 MSO (Version 2403 Build 16.0.17425.20124) 64-bit
Private Sub Worksheet_Calculate() Dim KeyCell As Range Dim PrintRange As Range Set KeyCell = Me.Range("A2") ' Cell A2 is being monitored Set PrintRange = Me.Range("A1:B3") ' Modify this range as needed If KeyCell.Value <> "Not found" Then PrintRange.Worksheet.PageSetup.PrintArea = PrintRange.Address PrintRange.Worksheet.PrintOut End If End SubIf this change doesn't help, please insert the file (without sensitive data) to see the example.
