Macro for Print Program

[Nimish]

Hi All:

I have a employee schedule in Excel that has names in Column A
(approximately 500 entries). I'd like to create a macro that could
print an individual page for each individual employee and place their
name in the header. Can you do this in Excel? or is this better
suited for Access?

Just curious if anyone has any experience with something like this.

 

[Ron de Bruin]

Hi Nimish

With names in A1: A10 try this

If it is working OK then change 10 to 500 and delete preview:=True

Sub PrintCopies_ActiveSheet()
Dim CopieNumber As Long

For CopieNumber = 1 To 10
With ActiveSheet
.PageSetup.LeftHeader = Cells(CopieNumber, "A").Value

'Print the sheet
.PrintOut preview:=True
End With
Next CopieNumber
End Sub

 

[Nimish]

This almost gets me there, except that it prints a copy of the ENTIRE
schedule for each employee. I rather just want to print out the
schedule for the "current page" of the employee row, thus only
providing the relevant page for each employee.

Anyone got that trick?

-Nimish

 

[Ron de Bruin]

I am not sure I understand you.

Can you give more details

 

[Nimish]

The macro you gave me work, except that the it does nothing to restrict
the print area to a header and the selected row for each employee. I
want to the printing so each employee would only get a printout for his
schedule.

 

[Ron de Bruin]

Is it only one row for each employee
Or is there also a header row that you want to print

 

[Nimish]

Row 1 will be a header row that I want to print.

Then it is one row for each employee.


So, print rows1 & rows(Copienumber) is how I want it.

 

[Ron de Bruin]

OK, try this

Row 1 is the header row so the first name is in A2
This example loop through the first 10 names and use AutoFilter
to filter on each name and then print

Sub PrintCopies_ActiveSheet_2()
Dim CopieNumber As Long

Application.ScreenUpdating = False
For CopieNumber = 2 To 11
With ActiveSheet
.PageSetup.LeftHeader = Cells(CopieNumber, "A").Value
.Range("A:A").AutoFilter Field:=1, Criteria1:=Cells(CopieNumber, "A").Value
'Print the sheet
.PrintOut preview:=True
.AutoFilterMode = False
End With
Next CopieNumber
Application.ScreenUpdating = True
End Sub

 

[Nimish]

Ron,

Thank you so much! I knew I could just use the autofilter to print,
but was having some trouble setting the criteria. Thanks so much for
your help.

-Nimish

 

[Ron de Bruin]

You are welcome