Autoprint worksheet

[anthony 1224]

Greetings Forum,
Win 8
Excel 2010

I am working with a simple workbook that contains a ledger worksheet and individual worksheets linked to the ledger. I am trying to find out how to create a macro that will automatically print and individual worksheet if a specific cell on the ledger is changed. For example if I enter an amount on ledger B2 which is the ledger entry spot for Customer 1 then I would like the worksheet named "Customer 1" to print.
Thank you for the opportunity to post my question.
Anthony

 

[anthony 1224]

Hi Joe the cell is labeled 1001 no quotations and the tab is labeled 1001 no quotations. I ran the code on the ledger worksheet there are no other macros running it gave a compile error in the vicinty of sheets(target.offset.....

 

[JoeMo]

Hi Joe the cell is labeled 1001 no quotations and the tab is labeled 1001 no quotations. I ran the code on the ledger worksheet there are no other macros running it gave a compile error in the vicinty of sheets(target.offset.....

Are you running the code with the ledger sheet active? Can you tell me exactly what the error message says and what line is highlighted when the error occurs? I've tested the code and it works fine for me so I really need the details to have any hope of diagnosing your problem.

 

[anthony 1224]

yes sir this is the line highlighted Sheets(Target.Offset(0, -1)).PrintOut the instruction is to change the worksheet name to suit but I dont know what or where to change
Thanks
Anthony

 

[JoeMo]

yes sir this is the line highlighted Sheets(Target.Offset(0, -1)).PrintOut the instruction is to change the worksheet name to suit but I dont know what or where to change
Thanks
Anthony

That comment line is a carryover from my original post and is no longer relevant, but no matter, now I understand your problem. It's not a compile error, it's that your tab name is 1001 not "1001". This should fix it. Remove the old code and copy/paste this in its place.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target(1)
If IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)) Is Nothing Then
    Sheets(CStr(Target.Offset(0, -1))).PrintOut
End If
End Sub

 

[anthony 1224]

Thank you sir, do I paste this code on the ledger worksheet or on the actual worksheet?
Thanks again
Tony

 

[JoeMo]

Thank you sir, do I paste this code on the ledger worksheet or on the actual worksheet?
Thanks again
Tony

Its for the ledger worksheet. Go back to post #2 and follow the instructions there. When tthe VBE window opens, remove any code that's there from prior posts and then paste the revised code in its place. Then change a cell in col B of the ledger sheet to test if the correct worksheet prints.

 

[anthony 1224]

Hi Joe,
This is working thank you for all of your help. If I were to add columns in the future which would shift the current column B to E would I just change the range in the code from B2 to E2?
Thanks again and I appreciate your efforts!
Anthony

 

[JoeMo]

Hi Joe,
This is working thank you for all of your help. If I were to add columns in the future which would shift the current column B to E would I just change the range in the code from B2 to E2?
Thanks again and I appreciate your efforts!
Anthony

You are welcome.

If you add columns in front of col A then change B to E in this line:

If Not Intersect(Target, Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)) Is Nothing Then

If you add columns in front of col B then ALSO change the -1 to -4 in this line:

Sheets(CStr(Target.Offset(0, -1))).PrintOut

 

[anthony 1224]

Thank you sir..yes I caught that -4 for the row skip. Thanks for your help its working fine!
Anthony

 

[anthony 1224]

how could I force this code to print to a specific printer we have several that are used and one specifically for this ledger which is not typically the default.
Thanks
Anthony