Printing time sheets from a list with a PrintAll Macro

[melaniez]

Hello


I started using a Time Sheet template built by somebody else and I'm wondering if there's a way to improve the "printall" macro it contains.


Basically I have one sheet that lists the employee with information in 5 columns.

Name - Department -Title - TITLE coding - DEPT coding

The other sheet is an empty time sheet template.


When I type an employee name into my time sheet, the rest of the information updates (simple Vlookup formula).


The current macro allows me to print the time sheets for all employees on the list, with a simple keyboard shortcut (CTR + S, even though the macro says Ctrl+Q - see below).

However, the current macro is recorded such as there's one code per row, which means I need to copy and paste the code in VBA and change the range every time I add an employee on my list. And if I remove employee from the list, I also need to remove the corresponding code otherwise it will print a blank sheet.


So I would like to be able to set up my printing range as the entire column("A:A"), then tell it to print only the rows that are filled.


Here's the current code:


' SelectAll Macro

' Macro recorded 9/7/2007 by

'

' Keyboard Shortcut: Ctrl+q

'


Sheets("Employee List").Select

Range("A2").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("TimeSheet").Select

Range("B4").Select

ActiveSheet.Paste

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True


End Sub


Thank you in advance for suggesting a different code!

 

[Hui]

Hi Melainez

Try the following subroutine instead of what you have

[pre]

Sub Print_Time_Sheet()
Dim c As Range
Sheets("TimeSheet").Select
For Each c In Worksheets("Employee List").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Range("B4").Value = c.Value
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next
End Sub

[/pre]

 

[melaniez]

Hi Hui


thanks for your input. I tried the code but it doesn't work: It keeps printing blank time sheets - I had to force quit to stop it from printing

Also, would it be possible to have a different shortcut for it (like ctrl + L for example, something that isn't already used).

Thanks !

 

[Luke M]

Modifying Hui's code a little:

[pre]

Sub Print_Time_Sheet()
Dim c As Range
Sheets("TimeSheet").Select
For Each c In Worksheets("Employee List").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If c.Value <> "" Then
Range("B4").Value = c.Value
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
Next
End Sub

[/pre]
To change the shortcut, you need to open the Macro window from within XL (Hit Alt+F8), select the macro, then click options to set the shortcut key.I tend to either use Ctrl+q, or I'll use a capital letter like Ctrl+Shift+p (for, special Print?)

 

[melaniez]

It works, Thanks Luke !

and thanks for the short cut tip as well!!

 

[melaniez]

OK, Something else came to my mind:

What if I want to single out some of the names in my list, and not include them in the batch print. This would be helpful for vacation/sick days that i need to manually enter on time sheets on a weekly basis. So i would print those one by one and then single them out so they don't print when I do the batch.


I know I could add an on/off function somehow, but I'm not sure how exactly.

thanks again for your help!

 

[Luke M]

Let's say that you use column F to mark the rows you want to exclude (either manually or via some formula, your choice) with an "X". Note that column F is 5 columns to the right of column A. Macro becomes:

[pre]

Sub Print_Time_Sheet()
Dim c As Range
Sheets("TimeSheet").Select
For Each c In Worksheets("Employee List").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
'Note that use of the offset method. This says to look 5 columns
'to the right of c (which is in column A)
If c.Value <> "" And UCase(c.Offset(0, 5).Value) <> "X" Then
Range("B4").Value = c.Value
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
Next
End Sub

[/pre]

 

[melaniez]

It works great, thank you again !

 

[Luke M]

You're very welcome. =)

 

[emma dcosta]

Hi There,


We had been using Replicon's timesheet which is web-based, integrates with MS Project and QuickBooks (or any app with an open API), and includes very powerful, customizable reports.