Excel and macro question, for VoG, perhaps?

  exdragon 09:03 18 Apr 2004

Our photographic club has just run a slide competition which is organised on a spreadsheet consisting of about 1500 rows and 7 columns, A to G. Each slide is entered on one row and its score is entered in col. F, with G being used for awards.

Each set of 50 rows (A-GT) needs to be printed off individually as soon as the score is entered into row 50, col F and then into each subsequent 50th row. At present, the operator just highlights the relevant area and presses print, but he needs to be pretty quick and accurate, as the scoring continues without a pause.

Is there a macro which will do this? I'm thinking perhaps about printing 1-50, then 1-100 but without 1-50, then 1-150 but without 1-100, but I don't know how!

Any help will be appreciated.

  VoG II 09:27 18 Apr 2004

Right click the sheet tab and select View Code.

Delete anything that is in the window. Then add something along the lines of

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address(False, False) = "F50" Or Target.Address(False, False) = "F100" Or Target.Address(False, False) = "F150" Then


End If

End Sub

This will print the whole sheet. You may wish to modify this to print only the last 50 rows. The easiest way to determine how to do this is to record a macro to see how to set the print area.

Hope this gets you started.

  exdragon 09:46 18 Apr 2004

Thanks, VoG - failed at the first hurdle! How do I get the double quotes round the F50?

  VoG II 10:33 18 Apr 2004

Well I would copy it from here and paste it in rather than type it all out.

Double quotes should be Shift plus the 2 key (the 2 near the top, not on the numeric keypad).

  exdragon 12:41 18 Apr 2004

Am I stupid or what?? Don't answer that, my brain obviously wasn't in gear this morning - thanks!

Try posting the following code in the ThisWorkbook


Public RowNumber As Integer

Private Sub Workbook_Open()

RowNumber = 0

End Sub


and this in the worksheet code sheet


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column <> 7 Then Exit Sub

RowNumber = Worksheets("Sheet1").Cells(2000, 7).End(xlUp).Row

If RowNumber Mod 50 <> 0 Then Exit Sub

ActiveSheet.PageSetup.PrintArea = "A" & RowNumber - 49 & ":" & "G" & RowNumber

Application.Goto Reference:="Print_Area"


End Sub


As the mark is entered in column G then the code checks whether it has reached an entry divisble by 50, if it has it then sets the print area and prints out the sheet.


  VoG II 23:45 18 Apr 2004

I don't really understand this

Public RowNumber As Integer

Private Sub Workbook_Open()

RowNumber = 0

End Sub

Why is this necessary as all variables will be set to defaults (e.g. 0) when Excel is started.

How is "Print_Area" defined?

Perhaps I'm having an off day :o(

"I don't really understand this "

As you so rightly point out this element of the code is not required, like you I was tired and did not notice that I had amended the code so that it was no longer needed.


Public RowNumber As Integer

Private Sub Workbook_Open()

RowNumber = 0

End Sub


How is "Print_Area" defined?

In the line below, Print_Area is set

ActiveSheet.PageSetup.PrintArea = "A" & RowNumber - 49 & ":" & "G" & RowNumber

1 all I suspect :)

Best wishes

  VoG II 21:33 20 Apr 2004

Sorry Whisperer.

  exdragon 16:51 23 Apr 2004

Thank you all - I only just got the emails about the answers today. I've passed this on to the poor chap who has to work it out!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?