Autoprint worksheet

A

anthony 1224

New Member
Joined
Apr 28, 2015
Messages
12
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
This is code for the ledger worksheet.
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target(1)
If IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B2")) Is Nothing Then
    Sheets("Customer 1").PrintOut  'Change worksheet name to suit
End If
End Sub
 
Upvote 0
Thank you sir this code does work on my ledger. This will only work on one ledger entry however, in my case I have 250 ledger rows and 250 associated worksheets how would I amend this so that it will work for ledger customer 2, 3 and so on down the list? I applied the code to the ledger worksheet as instructed and it did priint the correct worksheet.
Thank you again for your reply I have been searching hi and lo to try and figure this out!
Anthony
 
Upvote 0
anthony 1224 said:
Thank you sir this code does work on my ledger. This will only work on one ledger entry however, in my case I have 250 ledger rows and 250 associated worksheets how would I amend this so that it will work for ledger customer 2, 3 and so on down the list? I applied the code to the ledger worksheet as instructed and it did priint the correct worksheet.
Thank you again for your reply I have been searching hi and lo to try and figure this out!
Anthony
Click to expand...
The code I provided uses cell B2 to trigger the printing. Whenever a change is made to that cell, other than just deleting its contents, the sheet for customer 1 prints. If you want to trigger other sheets to print when changes are made to other cells in col B, I would suggest adding a list of customers and their associated cells in col B so the code can be adapted to handle multiple triggers. Can you place a list of customer sheet names somewhere on the ledger sheet that parallels the col B cells for those customers?
 
Upvote 0
Greetings sir,
I actually already have a list of customer sheet names on my ledger that parallel the col B cells. That list is located in the A cells.
Thank you,
Anthony
 
Upvote 0
anthony 1224 said:
Greetings sir,
I actually already have a list of customer sheet names on my ledger that parallel the col B cells. That list is located in the A cells.
Thank you,
Anthony
Click to expand...
Here's a revision you can try. Delete the previous code or just copy and paste this over it.
Code: 
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(Target.Offset(0, -1)).PrintOut 'Change worksheet name to suit
End If
End Sub
 
Upvote 0
Hi Joe,
I am afraid that didnt work for me at all and with my very limited VBA knowledge I am sure I did something wrong. So my workbook looks like this:
this is worksheet #296 named "ledger" in the workbook named account holders which has 296 worksheets being 295 fro account holders and the last worksheet is the ledger
cell A cell B
account payment
1001 $45 the data in cell B transfers to worksheet named "1001"
1002 blank no payment the data in cell B transfers to worksheet named "1002"
1003 $41 the data in cell B transfers to worksheet named "1003"
The task I hope to accomplish is when the payment is entered on the ledger for 1001 in column B that the worksheet named 1001(which is worksheet #1 of 296) will automatically print. If no payment entered as in the case of account 1002 then that worksheet doesnt print.
Thank you
Anthony
 
Upvote 0
anthony 1224 said:
Hi Joe,
I am afraid that didnt work for me at all and with my very limited VBA knowledge I am sure I did something wrong. So my workbook looks like this:
this is worksheet #296 named "ledger" in the workbook named account holders which has 296 worksheets being 295 fro account holders and the last worksheet is the ledger
cell A cell B
account payment
1001 $45 the data in cell B transfers to worksheet named "1001"
1002 blank no payment the data in cell B transfers to worksheet named "1002"
1003 $41 the data in cell B transfers to worksheet named "1003"
The task I hope to accomplish is when the payment is entered on the ledger for 1001 in column B that the worksheet named 1001(which is worksheet #1 of 296) will automatically print. If no payment entered as in the case of account 1002 then that worksheet doesnt print.
Thank you
Anthony
Click to expand...
Did you follow the steps in post #2 to install the code? If you do that then try just changing the first payment cell (B2?) and see if sheet 1001 prints. Am I correct in assuming that the sheet tab shows "1001" without the quote marks around it?
 
Upvote 0
yes sir I did follow the steps in 2..the first code did work the only issue I had with it was it only worked for the one worksheet. I put the code on the ledger worksheet and the tab 1001 did print. I just need a way now to trigger other sheets to print when their ledger cell B is changed. So I have 295 ledger entries linked to 295 different sheets.
Thank you
Anthony
 
Upvote 0
anthony 1224 said:
yes sir I did follow the steps in 2..the first code did work the only issue I had with it was it only worked for the one worksheet. I put the code on the ledger worksheet and the tab 1001 did print. I just need a way now to trigger other sheets to print when their ledger cell B is changed. So I have 295 ledger entries linked to 295 different sheets.
Thank you
Anthony
Click to expand...
You didn't answer this question:
Am I correct in assuming that the sheet tab shows "1001" without the quote marks around it? In other words, if col A say cell A2 on the ledger sheet contains 1001, is that exactly what the worksheet name is or is it "1001" as you indicated in post #7?

Can you also tell me, with the revised code, what exactly happens when you change cell B2 on the ledger sheet, any error message or just nothing happens? Any other macros in your workbook?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Guna13
  • Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
742
Guna13
Guna13
T
  • Solved
VBA to email pay slip
2
Replies
12
Views
1K
tayomi
T
G
  • Question
PDF Create button to manage output formatting
Replies
0
Views
704
grabrail
G
G
  • Solved
Print Multiple sheets, based on cell value
Replies
9
Views
902
HaHoBe
HaHoBe
A
  • Solved
Double-click event macro auto filter multiple columns on different worksheet
Replies
6
Views
678
ac7
A

Forum statistics

Threads
1,214,646
Messages
6,120,717
Members
448,985
Latest member
chocbudda

Share this page

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
Back
Top