Print Template automatically Filled with data in another worksheet

  • Thread starter Thread starter Murtaza
  • Start date Start date
M

Murtaza

I have a Form in Sheet2 like:

Name: B2
Company B3
Department B4
Emp #: B5

and list of Employees A2:D123 (more than 100 employees) with above details
in Sheet1.

I want to Print Sheet2 for each of the employee with its details. I know It
can be
easily done with Word Mail Merge option but the form created in Excel could
not be easily developed in MS Word.

Is there any solution.

Regards.
 
Murtaza,

You would need to use a macro:

Sub PrintEmOut()

Dim myCell As Range

For Each myCell In Worksheets("Sheet1").Range("A2:A123")
With Worksheets("Sheet2")
.Range("B2").Value = myCell.Value
.Range("B3").Value = myCell(1,2).Value
.Range("B4").Value = myCell(1,3).Value
.Range("B5").Value = myCell(1,4).Value
.Printout
End With
Next myCell

End Sub

HTH,
Bernie
MS Excel MVP
 
Dear Bernie:
Thanks for the suggestion, I have tried the macro but I am unable to
understand it, kindly provide the detailed instruction. Below are what i
exactly want (more clearly).

Regards,
Murtaza
--------------------------SAMPLE-------------------------------------------
In Sheet1 (details of employee or DATA):

Name Company Department Emp No. [Headings / first row]
Murtaza MyCompany My Dept 100 [ ]
Murtaza2 MyCompany2 My Dept2 101 [DETAILS]
Murtaza3 MyCompany3 My Dept3 102 [ ]

In Sheet2 (FORM):
NAME: ___________
COMPANY: ___________
DEPT. ___________
EMP. NO: ___________

REQUIRED PRINTOUTS FOR EACH ENTRY IN SHEET1:
-------------PAGE 1---------------
NAME: Murtaza
COMPANY: My Company
DEPT. My Dept
EMP. NO: 100
--------------------------------------

-------------PAGE 2---------------
NAME: Murtaza2
COMPANY: MyCompany2
DEPT. MyDept2
EMP. NO: 101
--------------------------------------
-------------PAGE 3---------------
SO ON.......

--------------------------END
SAMPLE-------------------------------------------
 
Murtaza,

The macro assumes that your data sheet is named "Sheet1" and that your
report sheet is named "Sheet2". You will need to change those names in the
macro if you actual sheet names are different. Other than that, the macro
works simply by changing the values on Sheet2 to correspond to those on
Sheet1, and loops through the 122 rows of data, and should produce exactly
the output that you show in your example.

HTH,
Bernie
MS Excel MVP

Murtaza said:
Dear Bernie:
Thanks for the suggestion, I have tried the macro but I am unable to
understand it, kindly provide the detailed instruction. Below are what i
exactly want (more clearly).

Regards,
Murtaza
--------------------------SAMPLE------------------------------------------ -
In Sheet1 (details of employee or DATA):

Name Company Department Emp No. [Headings / first row]
Murtaza MyCompany My Dept 100 [ ]
Murtaza2 MyCompany2 My Dept2 101 [DETAILS]
Murtaza3 MyCompany3 My Dept3 102 [ ]

In Sheet2 (FORM):
NAME: ___________
COMPANY: ___________
DEPT. ___________
EMP. NO: ___________

REQUIRED PRINTOUTS FOR EACH ENTRY IN SHEET1:
-------------PAGE 1---------------
NAME: Murtaza
COMPANY: My Company
DEPT. My Dept
EMP. NO: 100
--------------------------------------

-------------PAGE 2---------------
NAME: Murtaza2
COMPANY: MyCompany2
DEPT. MyDept2
EMP. NO: 101
--------------------------------------
-------------PAGE 3---------------
SO ON.......

--------------------------END
SAMPLE-------------------------------------------
Bernie Deitrick said:
Murtaza,

You would need to use a macro:

Sub PrintEmOut()

Dim myCell As Range

For Each myCell In Worksheets("Sheet1").Range("A2:A123")
With Worksheets("Sheet2")
.Range("B2").Value = myCell.Value
.Range("B3").Value = myCell(1,2).Value
.Range("B4").Value = myCell(1,3).Value
.Range("B5").Value = myCell(1,4).Value
.Printout
End With
Next myCell

End Sub

HTH,
Bernie
MS Excel MVP

know
It
Click to expand...
Click to expand...
 
Back
Top