Calendar that Prints Individual Names in a List

[ENB]

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!

 

[ENB]

I'm wondering, is this even feasible?

 

[mattrx731]

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]

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.)

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]

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).

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.

 

[ENB]

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?