Calendar that Prints Individual Names in a List

ENB

New Member
Joined
Jul 13, 2008
Messages
5
Okay so what I'm trying to accomplish is this:

2 Sheets.

The first sheet is a typical calendar (Already made).
The second is a list of employees that can be added to or removed from.

What I want to happen is someone opens up the calendar and hits a button (or macro???) that will take the first employee name in the list and put it in a cell on the calendar sheet. Then, it will print off, move on to replace the name with the next one, print, etc, until all employees are printed. (Each employee will have their own calendar with their name on it.)

So how might I go about doing this?

Thanks!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

mattrx731

Board Regular
Joined
Feb 10, 2008
Messages
181
Not sure, but I think recording a macro manually would get you a good start. Post the code here and folks are pretty good about cleaning it up and modifying it.
 

ENB

New Member
Joined
Jul 13, 2008
Messages
5
Okay, this is what I've basically got so far. I would just copy/paste this over and over until I get as many as the amount of employees.

(Not sure why cell A1 is somehow "-4" and counts up...)

The only problem this way is that if an employee leaves or they hire someone, then the macro needs fixing. And no one will be their to fix it. (I'm trying to somehow automate this whole process.)

Code:
Sub testMacro()
'
' testMacro Macro
'

'
    Range("B6").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ActiveCell.FormulaR1C1 = "=Employees!R[-4]C[-1]"
    Range("B6").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ActiveCell.FormulaR1C1 = "=Employees!R[-3]C[-1]"
    Range("B6").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub
 

ENB

New Member
Joined
Jul 13, 2008
Messages
5
Well, I'm figuring something as I go along on my own. However, I've run into some trouble here.

What I'm trying to do now is see if a main "label" cell is empty/0/or has a formula but "looks" empty. If it is it should jump down to the msgbox.

NOTE: I have a list of employees and I'm making the list have 50 entries but the last 20 or so will be blank cells (or potentially 0).

Code:
Range("A6:D6").Select
    
    Do While (ActiveCell = 0)
    
    
    ActiveCell.FormulaR1C1 = "=Employees!R[-5]C"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Range("A6:D6").Select
    ActiveCell.FormulaR1C1 = "=Employees!R[-4]C"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Range("A6:D6").Select
    ActiveCell.FormulaR1C1 = "=Employees!R[-3]C"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Range("A6:D6").Select
    ActiveCell.FormulaR1C1 = "=Employees!R[60]C"            ///////// This command puts in a zero. The actual cell it takes it from is purely blank.
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Range("A6:D6").Select
    
    Loop
    
    MsgBox "Print Job Completed!", vbInformation, "Completed"
Please let me know if you have any ideas on anything. Thanks.
 
Last edited:

ENB

New Member
Joined
Jul 13, 2008
Messages
5
I just fixed it a little bit. I put "Do While NOT" instead of just "Do While". However, it still prints the last one in the list (That one puts a zero in the label). How can I test it before it prints?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

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