From excel data create report cards of exam

[Madhurima]

Dear sir,

we have excel sheets to collect data and written formulas(in merged sheet) to calculate result.
then we are using word to merge it and to generate report cards.
How we can generate the report cards for all the students in excel itself.

I am uploading the excel sheet as well as the word document showing the format of report card

 

[Hui]

Madhurima

Have a look at the attached file

The Report worksheet is linked to the Selected Student Row 4 on the Printouts worksheet

The macro changes the values in B4 and then prints out a new report
It prints to your default printer

You can adjust the Column Widths etc to get the printout looking right

I haven't linked the yellow cells to Row 4 yet, you can do that

 

[pecoflyer]

What's wrong with Word's "Mail merge"?

 

[Madhurima]

Thank you so much sir . can you explain the code written in reports for print all report button .How to access the print preview of all report card.

please explain in detail.

 

[Hui]

Pls see the attached file
It has revised code that is fully documented

It also now has the option to preview the reports first

 

[Madhurima]

Dear sir,

Opened the revised attached file. But its same as the previous one. Only
one report card gets printed. print preview not available. When clicking on print all reports. The file goes into loop and no printout comes.

I am using version 2007.

Refer the attached file for errors.

kindly explain in "report " sheet which things you have done.
I understood that you have done the copying of reportcard format
and then data cell is coming from the printout sheet.

My question is how to preview and print the report card of all 40 students.
what coding is written in the print all reports.

 

[Hui]

From the error messages you have lost the formatting on the report worksheet. I am sure you can add them back in

I have never used Excel 2007 and so cannot test for compatibility

The Report worksheet looks like:


The Report worksheet is linked to the Selected Student Row 4 on the Printouts worksheet. I have only linked the cells above the green area to Selected Student Row 4 on the Printouts worksheet.

You can reformat the Report worksheet as required

I have added a button to allow you to preview the reports without changing the code

 

[Madhurima]

you are working in which version of MS Excel.

 

[Madhurima]

from which menu the buttons have been inserted.what coding is written in buttons

print all reports
print preview all reports
print current report

 

[Hui]

I only work in Excel 2016

The Buttons are shapes on the worksheet

You can link the Buttons to the appropriate macros, which are named according to what they do

Right click on each Button, select Assign Macro
select the appropriate macro

 

[Madhurima]

Refer the attached file 11A.data is coming from printout sheet to the report card.
I have displayed only those rows and column from which data has to be displayed on the report sheet.How it will change

can you explain this code.
Dim LR As Integer
LR = Worksheets("Printouts").Range("B" & Rows.Count).End(xlUp).Row

'Step through the students from Student 1 to Stundent in the Last Row
For i = 5 To 7
'LR
'Set the active student to Student i
Worksheets("Printouts").Range("B4").Value = Worksheets("Printouts").Range("B" & CStr(i))
'Calculate the workbook

 

[Madhurima]

Dear sir,

We are facing problem to display date. its changing month to year and year to month when we are converting it to text.
for exmaple: we have entered date in a1 column as 4/09/2014(format is date)
but when we are converting to text by writing =text(a1,"dd/mm/yyyy") then its changing it to 4/9/2014.

But its not happening always . It behaves weiredly.

 

[Madhurima]

Thankyou so much for your prompt reply

 

[Hui]

Refer the attached file 11A.data is coming from printout sheet to the report card.
I have displayed only those rows and column from which data has to be displayed on the report sheet.How it will change

can you explain this code.
Dim LR As Integer
LR = Worksheets("Printouts").Range("B" & Rows.Count).End(xlUp).Row

'Step through the students from Student 1 to Stundent in the Last Row
For i = 5 To 7
'LR
'Set the active student to Student i
Worksheets("Printouts").Range("B4").Value = Worksheets("Printouts").Range("B" & CStr(i))
'Calculate the workbook

Look at my comments in Green!
They tell you what each block of code does

'This gets the Last row number of the students in the Printouts worksheet
Dim LR As Integer
LR = Worksheets("Printouts").Range("B" & Rows.Count).End(xlUp).Row

the line
For i = 5 To 7 'LR
should be
For i = 5 To LR

the 7 was for testing as I don't want to print out 40 reports

But once again the comments tell you this:

'Step through the students from Student 1 to Stundent in the Last Row
For i = 5 To LR

is LR is a variable name I use to hold the Last Row

 

[Hui]

Dear sir,

We are facing problem to display date. its changing month to year and year to month when we are converting it to text.
for exmaple: we have entered date in a1 column as 4/09/2014(format is date)
but when we are converting to text by writing =text(a1,"dd/mm/yyyy") then its changing it to 4/9/2014.

But its not happening always . It behaves weiredly.

So change the date format to suit your requirements

ps: I don't know how this question relates to the issue as I don't use that formula anywhere? None of the worksheets have a date in Column A ?

 

[Hui]

Your file 11A

1. Has no macro's
2. Does not have a Selected Student record in the Printouts worksheet

If you aren't going to follow the example I gave you that works, how can I be expected to assist you ?