Vb code for selected area in sheet

Question

Please check attached image for my question. In my sheet there is 5 students named A,B,C,D,E. They have marks for Subject 1, 2, 3 ( Sub1 , Sub2, Sub3 ). In Image 1 student "C" & "E" they didn't take any marks for any subject. I want to create a print button for this sheet like when i press the print button create a new sheet (Not in same sheet) and take the details who has taken marks for subjects. Image 2 is example. In Image 2 there is no student named "C" and "E". Please Help Eran

Answer

Eran

Here is hwo I would approach the problem

First I would create a total of all the marks for each student in column E or any other column adjacent to the grades using the formula =sum(B1:D1). This would isolate the students with a grade in any subject. Then I would use an IF formula to transfer only the students with marks to another sheet. The formula might look like this In sheet 2 cell A1 =+IF(Sheet1!$E1>0,+Sheet1!A1,"")

This formula says if the cell that summed the marks (cell E1) in sheet1 is greater than zero, then bring over the value in sheet1, cell A1 which is the student name. This formula can be copied to the right and down to bring over all of the marks relative to those that have marks (note the $ in front of cell E1 in the formula which makes it absolute and gives you the ability to drag or copy the formula to the right and down.

From this point I would Sort the range of cells in sheet2. This sort is intended to eliminate the rows that have no data in them and group the cells that do have data in them without any empty rows between data. The sort range can be much greater than that which contains data so that the next time you use the sheet it will sort the entire potential area of values. In other words it it is possible that your data range could be 100 rows, then your sort should contain 100 rows.

After sorting you can then print the data that you need. If you want to automate this procedure you can create a macro that will do the sort and print of the data range simply by recording your keystrokes while using the feature Record macro on the toolbar, at toolbar/tools/macro/record macro. You can further automate the running of the macro by creating a button on the sheet and assigning the macro to the button, thereby enabling the user to click the button and run the macro. Check out thehelp relative to creting a button and assigning the macro.

Question

Please check attached image for my question. In my sheet there is 5 students named A,B,C,D,E. They have marks for Subject 1, 2, 3 ( Sub1 , Sub2, Sub3 ). In Image 1 student "C" & "E" they didn't take any marks for any subject. I want to create a print button for this sheet like when i press the print button create a new sheet (Not in same sheet) and take the details who has taken marks for subjects. Image 2 is example. In Image 2 there is no student named "C" and "E". Please Help Eran

Answer

Surely the easiest process would be for the macro to hide the rows that have no data, then print the sheet without copying anything anywhere (unless you need a copy) and then unhide the rows again.

My email if you wish to discuss this further directly with me is aidan.heritage@virgin.net