Excel and macro question, for VoG, perhaps?

  exdragon 09:03 18 Apr 04
Locked

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 04

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

ActiveSheet.Printout

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 04

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

  VoG II 10:33 18 Apr 04

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 04

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"

Selection.PrintOut

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.

HTH

  VoG II 23:45 18 Apr 04

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 04

Sorry Whisperer.

  exdragon 16:51 23 Apr 04

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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

These are the Best Christmas Ads and Studio Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…