Printing in EXCEL 2007

June 14, 2010 at 03:55:28
Specs: Windows Vista
Hi Again. :)

Am using a dropdown list with vlookup function to retrive some data

its actually retriving student details

for example

when i choose 1 from the drop down list

it will retrive for me

the student name his id number his result ( academic information)

but my problem that am retriving these data for 650 students

it takes so much time for printing them one by one .

My question is

is there any way that i can print all these student more eaisly. but i need the drop down list because without it i cannot retrive all the student details.

please help me.

regards,

Maram


See More: Printing in EXCEL 2007

Report •


#1
June 14, 2010 at 04:43:28
Perhaps you need to explain what you are doing in a little more detail.

If you want to print "all these student more easily" why not just print the range that the VLOOKUP is pulling the data from?

I'm sure it's not that simple, but you'll need to tell us why before we can help any further.


Report •

#2
June 14, 2010 at 05:33:13
Hi,

You can use a macro to populate the drop-down, and then print each record.

You haven't given any details of the addresses of the drop-down cell or the cells containing the list of names, or the range of cells to be printed.

You will have to change the addresses and worksheet name used in this example to match your setup.

The example code uses the current printer setting for Excel. You can force it to use a specific printer (see note in the code)
the print line then looks like this:
.PrintOut ActivePrinter:="Printer Name"

Sub PrintAll()
Dim n As Integer
With Worksheets("Sheet1")
    'set print area
    PageSetup.PrintArea = "A1:C5"
    'loop through names for drop-down
    For n = 0 To 103
        'drop down in B4 and first name in list in G4
        'change as required.
        'apply each name to drop-down cell
        .Range("B4") = .Range("G4").Offset(n, 0).Value
        'print each record (uses current printer)
        'add printer name if required ActivePrinter:="Printer Name"
        .PrintOut
    Next n
End With
End Sub

If this works, you could embed a button and attach the code to the button to make it easier to use. You could add a 'confirm' message just to confirm that you want to print all the records just in case the button was clicked by accident or that the wrong printer was selected.

Regards


Report •

#3
June 14, 2010 at 05:36:29
Hello Mr.Derby

Actually Mr. Derby am Doing a Student reports using microsoft Excel 2007

I have an Excel sheet which contains the following Columns

Student Name / Id Number/ Grade/ Math/ science/english ....
etc

and i have the report design in another sheet i the same work book

it contains the design and a drop down list

because when i choose student number from the drop down it must retrive all the student details on the right reports fileds

like if i choose derby it will retrive all derby academic fileds ( derby report) if i choose 2 ( it will returm maram fileds) ........................ 650 students.

pleases help me

regards
Maram


Report •

Related Solutions

#4
June 14, 2010 at 05:38:42
Mr. Humar

Hello

Sir I didnot understand how to do it, what must i do first

Help me

I never used macros before

explain it for me

regards,
maram


Report •

#5
June 14, 2010 at 06:04:40
Mr. Humar

am trying to do it but its not working

i really dont know how

i never used macros before

can i send you my program

if you can help me please

or can you make me a small example

coz i really dont under stand

regards,
maram


Report •

#6
June 14, 2010 at 06:14:09
Hi,

To run the macro, I suggest you add a button to your source worksheet.
From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit

Sub Button1_Click()
Dim n As Integer
With Worksheets("Sheet1")
    'set print area
    PageSetup.PrintArea = "A1:C5"
    'loop through names for drop-down
    'change number to match number of student names in list -1
    For n = 0 To 649
        'drop down in B4 and first name in list in G4
        'change as required.
        'apply each name to drop-down cell
        .Range("B4") = .Range("G4").Offset(n, 0).Value
        'print each record (uses current printer)
        'add printer name if required ActivePrinter:="MyPrinter"
        .PrintOut ActivePrinter:="Colour Laser"
    Next n
End With
End Sub

Note that Sub Button1_Click() and End Sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 (The Alt key and Function key 11 pressed together) takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful, such as "Print all records"

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

In Excel select Print and select the printer to be used, then Cancel the print dialog box.

After selecting any cell, the new command button should now respond to a click and run the macro.

Each name in the list of names should go into the drop-down and the selected print area will be printed to the selected printer for each name.

To test the macro, you may want to limit the number of records printed - just use:
For n = 0 to 2
and you will get the first three records printed.

Regards


Report •

#7
June 14, 2010 at 06:20:41
Am going to try it now
:)

Report •

#8
June 14, 2010 at 06:38:20
Mr Humar

there are a few things that i dont understand

Option Explicit

Sub Button1_Click()
Dim n As Integer

With Worksheets("Sheet1")

/////// sheet1 which sheet you mean ( do you mean the sheet which i have the design in ???)
coz in this sheet i have the drop down list and the vlookup options.


'set print area
PageSetup.PrintArea = "A1:C5"

//////// you mean the print area for the the sheet which i have the designed report in or the the sheet which i have the tables of data.

'loop through names for drop-down
'change number to match number of student names in list -1
For n = 0 To 649
'drop down in B4 and first name in list in G4 /// i didnot understand
'change as required.
'apply each name to drop-down cell //// i didnot understand
.Range("B4") = .Range("G4").Offset(n, 0).Value //// didnot understand

'print each record (uses current printer)
'add printer name if required ActivePrinter:="MyPrinter"
.PrintOut ActivePrinter:="Colour Laser"
Next n
End With

End Sub

iam trying to doing it

can you give me a small example

like


sheet 1

student No. Student Name Grade Class Id.No Mark1 Mark2 Symbol Ranking

1 Humar 7 A 9090909090 70 90 100 First A+


sheet 2

i have a drop down list when i choose 1 it will retrive for me humar with all his details

you mean i will put the butoon in sheet 2 right.

please help me

regards,
Maram


Report •

#9
June 14, 2010 at 07:03:17
I want to cry its not working its giving me many errors
variables not defined

help me :'(


Report •

#10
June 14, 2010 at 11:04:41
Hi,

1. Sheet name
The button goes on the worksheet that has the drop-down list and the Vlookup formulas
As a result the code needs to reference the same sheet.
So change "Sheet1" to match the name of the worksheet that contains the drop-down/vlookups.

2. Print Area
As you did not give any details about where everything was located, the print area was setup on the same worksheet as the drop-down/vlookups.

2a. Change the print area range to the range of cells you wish to print

2b. If the print area is on a different worksheet, then the code will have to activate that sheet before printing - you will need to supply the name of the worksheet and the range of cells to be printed, and I will modify the code accordingly.

3. 'drop down in B4 and first name in list in G4 /// i didnot understand
You have a drop-down - that drop-down is in a cell. Change B4 to the address of the cell containing the drop-down
The drop-down is populated using a List in the data validation for that cell. The list of names starts in a particular cell. Change G4 to the address of the first cell in the list.

4. 'apply each name to drop-down cell //// i didnot understand
.Range("B4") = .Range("G4").Offset(n, 0).Value //// didnot understand
When writing macros, I include text which identifies what the code is doing.
You do understand that lines starting with a ' are just comments - they are not code.
In this case I am identifying that the code will 'apply each name to drop-down cell'
Instead of you manually selecting each student's name, this code will do it for you.
.Range("B4") is the address of the cell containing the drop-down, that you are going to change to the correct cell - because you didn't give any information about cell addresses - see item 3 above.
.Range("G4").Offset(n, 0).Value - is a function that takes each name in your list of names. If G4 contains the first name then Offset will allow the For-Next loop to find successive names in the list. Offset uses two arguments Offset(Row, Column) and then copies the value in the cell to the drop-down cell. As n is used for the Row, the row offset will change with each For - Next loop - n will go from zero to however many names you have specified.

5. can you give me a small example
The code is specific to the data you have.
I have tested this on some sample data, but as I don't know where you have all your data there is little point in providing a sample. My test data was data that I was able to create automatically - I didn't create lists of real names or results - I just don't have time to create 'real' data.
If you make the changes to the code, so that the addresses and worksheet name(s) match what you have, then you can try it out - but always use a backup copy for testing macros as any changes cannot be undone with the undo button.

6. sheet 2
i have a drop down list when i choose 1 it will retrive for me humar with all his details
you mean i will put the button in sheet 2 right.
See item 1.

From your comment (item 6) are you actually selecting students records by a number rather than their name.

What data validation list are you using for the drop-down.

If you are not using the student names as the drop-down list, are you using 1 to 650. If so is there any reason for not using the student's names in the drop-down.

Regards


Report •

#11
June 14, 2010 at 11:43:12
Hi Mr. Humar

I Have a sheet called " Scores" it has the following column

Student Number Student Name ID.NO MATH SCIENCE ENGLISH SPORT GRADE

Its have an Information for 650 students.
( all my data are there)

also i have another sheet with some other marks for the students. called " Grades'

I have another sheet in the same work book called " Report"

it has the report design there for the student.


I made a drop down list using data validaition from 1 to 650

iam using students numbers coz its easier than using their names

i used a vlookup formula in the same sheet and connected with drop downlist

in each cell in the design report i write a command of v lookup to take from both sheets "scores", "grades"

if i select number 1 first student will be retrived with all its in formation from the both sheets ( then i will choose print to print it)
then am going to choose 2 for the second student to rerivet and soooo on until the last student , its taking so much time for retriving all the students

i put the drop down list in a cell called "L9"
the report design is on the range from a1 until k75

for retriving data using vlookup
i select the all the cell which have the data and name it maram1 for the score sheet

and i select all the cells which have data in the grade sheet and name it maram2

some cells i used
= vlookup($L;Maram1;2;false);

and some cells i used
= vlookup($L;Maram2;2;false);

this is the vlookup function i used it in the report sheet which i need to print

i hope that i have answered all your question

pleases help me

am going to try what you have told me i your last reply now

but please help me

thank you for giving me from your precious time
with all my respects
Maram


Report •

#12
June 14, 2010 at 12:01:25
Mr. Humar

Its giving me an compile error
veriable not defined and its highi lighted Pagesetup

and sir For the G4 in you example do you mean

the other 2 sheets which am retriving the data from

it has first the student number then then the student name and so on

actually iam using the students numbers just for retriving coz i need to print from the second column so maybe as i understant from you it will begin from the student name which i retrivet from the vlookup refrence as 2 coz its in the second coumn.

please help me,
regards,
maram


Report •

#13
June 14, 2010 at 12:20:02
Mr. Humar I fixed the compile error you need to type it like this:

ActiveSheet.PageSetup.PrintArea = "A1:L57"

but i am not sure about the data range i mean for the G4 in your example.

i will do my best too

but please help me.

regards,
Maram


Report •

#14
June 14, 2010 at 12:31:46
Hi,

Can you confirm the worksheet names.

You have used different names in different parts of your post.

Names must be accurate.

Is it " Grades", or "Grades", or "grades"
and the same for other sheets.

You refer to the drop-down:
I made a drop down list using data validaition from 1 to 650
but which worksheet is it on.

The range to print seems to be A1:K75
Is this the range you want to print and please confirm the name of the worksheet it is on.

Also you have:
= vlookup($L;Maram1;2;false)
Is this correct
Is the lookup value in a cell $L
Also are you using ';' between the arguments, or is it ','

Regards


Report •

#15
June 14, 2010 at 13:15:52
Hi Mr. Humar

There is a sheet called "Grades" which have students data
also their is "Scores" its have also data

and the "Reports" sheet which has the report dsign and the vlookup and the drop down list

this is my vlookup

=VLOOKUP($L9;Maram1;2;FALSE)

you cannot put (,) it must be (;) for excel 2007


the drop down is on the "Reports" sheet

((((( The range to print seems to be A1:K75
Is this the range you want to print and please confirm the name of the worksheet it is on.)))))

yes this is actually the report design which i want to print

it is on the "Reports" sheet too.

the name of the hall work book is "Students" which has the three sheets "Grades","Scores","Reports"

$L9 (it is correct) and the vlookup is correct

but please sir notice that iam retriving from 2 sheets not from one

am retriving from "Scores","Grades" sheets as i reply for you before

hope that i answered all your questions

regards,
maram


Report •

#16
June 14, 2010 at 16:41:47
Hi,

To run the macro, I suggest you add a button to the "Reports" worksheet.

Select the "Reports" worksheet.

From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.
Draw the button on the "Reports" worksheet - but draw it outside the print area, i.e., outside A1:K75
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit

Private Sub Button1_Click()
Dim n As Integer

With Worksheets("Reports")
    'set print area and print parameters
    With .PageSetup
        .PrintArea = "A1:K75"
        .Orientation = xlPortrait
        .LeftFooter = "Printed on: &D"
        .LeftMargin = Application.InchesToPoints(0.590551181102362)
        .RightMargin = Application.InchesToPoints(0.590551181102362)
        .TopMargin = Application.InchesToPoints(0.393700787401575)
        .BottomMargin = Application.InchesToPoints(0.393700787401575)
        .HeaderMargin = Application.InchesToPoints(0.31496062992126)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
        .CenterHorizontally = True
        .CenterVertically = False
        .Draft = False
        .PaperSize = xlPaperLetter
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    'loop through all records 1 to 650
    For n = 1 To 650
        'change value in L9 drop-down
        .Range("L9") = n
        'print each record on named printer
        .PrintOut
    Next n
End With
End Sub

Note that Sub Button1_Click() and End Sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().

Note that after 'With PageSetup' line there are a series of features that you can set as required. (I did not include some of the default settings). In particular you can set the page size and orientation and force the report to fit on to one page.
I included the 'Printed on:' date in the left footer, just as an example of what you can do.
(.LeftFooter = "Printed on: &D")
Page margins can also be set.

Click Save from the Visual Basic Menu.

Then hit Alt+f11 (The Alt key and Function key 11 pressed together) to take you back to the main Excel window.

Right click the button and Edit the name to something meaningful, such as "Print all records"

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

In Excel select Print and select the printer to be used, then Cancel the print dialog box. This sets the printer to be used by the macro. Alternatively you can change the line:
.PrintOut
to include the printer name, e.g.,
.PrintOut ActivePrinter:="My Printer"

After selecting any cell, the new command button should now respond to a click and run the macro.

The macro will select and print all 650 records.

To test the macro, you may want to limit the number of records printed - just change the 'For n =' line to:
For n = 1 to 3 instead of For n = 1 to 650
and you will get the first three records printed.

The fact that you are taking data from two other worksheets does not matter.

This macro just does what you did manually - changes the number in cell L9 on the "Reports" page and then prints the range A1:K75" and then does this 650 times.

Regards


Report •

#17
June 14, 2010 at 23:43:37
Mr . Humar it worked with me but i have a big problem

when i closed Ms Excel

and open it again it dinot work

it says

cannot run macro the macro may not be available in this work book all macros may be disabled

please help me

how can i enter the macro
i need to copy the code

please help me


Report •

#18
June 15, 2010 at 05:03:06
Hi,

As you are using Excel 2007 you will need to save the workbook in a macro-enabled format.

I don't have Excel 2007 on this PC, so I can't be sure of the details, but when you come to save the workbook after entering the button & macro, select the Macro-enabled file type from the list.

If you go to the Developer tab there should be an option for Entering Design mode.
You will then be able to right-click the button and select View code, to see if the code is still there. If not re-enter it.

Then save in a Macro-enabled format.

Regards


Report •

Ask Question