Macro or other automated method to update and print a worksheet

Chuck6475 · Sep 30, 2012

I developed a spreadsheet to score golf tournaments, do skins, various flights, etc. to help the Men's Club. It has been fun but an Excel expert, I'm not. If I could solve the problem below without using a macro that would be preferred.

Once upon a time I was a programmer, but that's moons ago. I've never written any VB or Macros for Excel. I'm using 2003 on W7 but the club uses 2007 on XP, I think.

My issue is using Excel to print the scorecards. I've developed a worksheet to print scorecards by group number. As a group number changes so does a great deal of the data on the scorecard to be printed.

I'm looking for a method to not have to manual change the group number and print the scoresheet.

I found a macro written by Jbeaucaire in May 2010 on this site that looked promising but I don't understand the code well enough to know how to modify it to work. Here is that code:

Option Explicit

Sub IterateValues()
Dim CpyRNG As Range, MyVal As Range
Application.ScreenUpdating = False

Set CpyRNG = Sheets("Data").Range("A:A").SpecialCells(xlCellTypeConstants)

For Each MyVal In CpyRNG
With Sheets("Form")
.Range("A1").Value = MyVal.Value
.Calculate
.PrintOut Copies:=1
End With
Next MyVal

Set CpyRNG = Nothing
Application.ScreenUpdating = True
End Sub

The Group number is sequential from 1 to some tournament max. The printed material is the same on each card but the data changes depending upon the group number.

Thanks in advance for your help.

AlphaFrog · Sep 30, 2012

Hi and welcome to the forum.

You gave good background details, but were a liitle sparse on providing specifics. Where (Sheet name and column or row) do you find the max group number? Where (Sheet name and cell) do you want to put the group numbers; 1 to max?

Try something like this. Edit the red parts to suit.

Code:

[COLOR=darkblue]Sub[/COLOR] Print_ScoreCards()
    
    [COLOR=darkblue]Dim[/COLOR] Group [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR], MaxGroup [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    MaxGroup = Application.WorksheetFunction.Max([COLOR=#ff0000]Sheets("Sheet1").Range("A:A")[/COLOR])
    
    [COLOR=darkblue]With[/COLOR] Sheets([COLOR=#ff0000]"ScoreCard"[/COLOR])
        [COLOR=darkblue]For[/COLOR] Group = 1 [COLOR=darkblue]To[/COLOR] MaxGroup
            .Range([COLOR=#ff0000]"A1"[/COLOR]).Value = Group
            .Calculate
            .PrintOut Copies:=1
        [COLOR=darkblue]Next[/COLOR] Group
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Sheets("Sheet1").Range("A:A") is the sheet name and column with the tournament group numbers. The code will determine the max value in that range

"ScoreCard" is the sheet name that you want to print
"A1" is the cell locatiion on the "ScoreCard" sheet that recieves the group numbers 1 to max.

Chuck6475 · Oct 1, 2012

AlphaFrog said:
Hi and welcome to the forum.

You gave good background details, but were a liitle sparse on providing specifics. Where (Sheet name and column or row) do you find the max group number? Where (Sheet name and cell) do you want to put the group numbers; 1 to max?

Try something like this. Edit the red parts to suit.

Code:

[COLOR=darkblue]Sub[/COLOR] Print_ScoreCards() [COLOR=darkblue]Dim[/COLOR] Group [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR], MaxGroup [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR] Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR] MaxGroup = Application.WorksheetFunction.Max([COLOR=#ff0000]Sheets("Sheet1").Range("A:A")[/COLOR]) [COLOR=darkblue]With[/COLOR] Sheets([COLOR=#ff0000]"ScoreCard"[/COLOR]) [COLOR=darkblue]For[/COLOR] Group = 1 [COLOR=darkblue]To[/COLOR] MaxGroup .Range([COLOR=#ff0000]"A1"[/COLOR]).Value = Group .Calculate .PrintOut Copies:=1 [COLOR=darkblue]Next[/COLOR] Group [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR] Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Sheets("Sheet1").Range("A:A") is the sheet name and column with the tournament group numbers. The code will determine the max value in that range

"ScoreCard" is the sheet name that you want to print
"A1" is the cell locatiion on the "ScoreCard" sheet that recieves the group numbers 1 to max.

_________________________________________________

Thanks and sorry for not enough detail.

Sheet name and column or row for MaxGroup number is: (Sheet name) Tee_Times
(cell) A1

Where do you want to put the group numbers is: (Sheet name) Stroke_dot_Cards
(cell) w1

So I did this:

Sub Print_ScoreCards()
Dim Group As Integer, MaxGroup As Integer

Application.ScreenUpdating = False

MaxGroup = Application.WooksheetFunction.Max(Sheets("Tee_Times").Range("a1:a1"))

With Sheets("Stroke_dot_Cards")
For Group = 1 To MaxGroup
.Range(w1).Value = Group
.Calculate
.PrintOut Copies:=1
Next Group
End With

Application.ScreenUpdating = True
End Sub

--------

It doesn't like the MaxGroup statement and I get a Run-time error '438' Object doesn't support this property or method.

AlphaFrog · Oct 1, 2012

Try this...

Code:

[COLOR=darkblue]Sub[/COLOR] Print_ScoreCards()
    
    [COLOR=darkblue]Dim[/COLOR] Group [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Sheets("Stroke_dot_Cards")
        [COLOR=darkblue]For[/COLOR] Group = 1 [COLOR=darkblue]To[/COLOR] [COLOR=#ff0000]Sheets("Tee_Times").Range("A1").Value[/COLOR]
            .Range([COLOR=#ff0000]"W1"[/COLOR]).Value = Group
            .Calculate
            .PrintOut Copies:=1
        [COLOR=darkblue]Next[/COLOR] Group
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

This assumes that Sheets("Tee_Times").Range("A1").Value already contains the max group number.

Chuck6475 · Oct 1, 2012

Thanks it works great. Now I'm off to learn more VBA.

Chuck6475 · Oct 2, 2012

So now with an "ounce" of knowledge, I decided to alter to code to handle multiple scorecard formats. The formats are controlled by a value in sheet "Course" Cell "w16".

Haven't made it to the bookstore yet, so I have been using Google queries to find VBA guidelines.

It appeared that VBA supported a "true" IF statement, meaning a one line IF with a single argument, so here is what I came up with.

Sub Print_ScoreCards()
Dim Group As Integer, MaxGroup As Integer
Dim Card As Integer

Application.ScreenUpdating = False

Card = Sheets("Course").Range("w16").Value

If Card = 1 Then With Sheets("Stroke_dot_Cards")
If Card = 5 Then With Sheets("Match_Play_Card")
If Card = 2 Then With Sheets("OrangeBall")

For Group = 1 To Sheets("Tee_Times").Range("A1").Value
.Range("W1").Value = Group
.Calculate
.PrintOut Copies:=1
Next Group
End With

Application.ScreenUpdating = True
End Sub

_______________________________________

Result is an error message "End if without block if" it doesn't work any different if I put an "End if" after each IF statement on a separate line.

AlphaFrog · Oct 2, 2012

Good try. I suggest something like this...

Code:

[color=darkblue]Sub[/color] Print_ScoreCards()
    
    [color=darkblue]Dim[/color] Group   [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    [color=darkblue]Dim[/color] wsPrint [color=darkblue]As[/color] Worksheet
    
    [color=darkblue]Select[/color] [color=darkblue]Case[/color] Sheets("Course").Range("W16").Value
        [color=darkblue]Case[/color] 1: [color=darkblue]Set[/color] wsPrint = Sheets("Stroke_dot_Cards")
        [color=darkblue]Case[/color] 5: [color=darkblue]Set[/color] wsPrint = Sheets("Match_Play_Card")
        [color=darkblue]Case[/color] 2: [color=darkblue]Set[/color] wsPrint = Sheets("OrangeBall")
        [color=darkblue]Case[/color] [color=darkblue]Else[/color]
            MsgBox "Missing course number in cell W16. ", , "Course Number Missing"
            [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]Select[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [color=darkblue]With[/color] wsPrint
        [color=darkblue]For[/color] Group = 1 [color=darkblue]To[/color] Sheets("Tee_Times").Range("A1").Value
            .Range("W1").Value = Group
            .Calculate
            .PrintOut Copies:=1
        [color=darkblue]Next[/color] Group
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

TIP: take note my signature block below and surround your code using Code tags.
[CODE]your VBA code here[/CODE]

It makes reading VBA code a little easier.

Chuck6475 · Oct 2, 2012

I was just about to post that I had figured out it was the "with" statement that was causing my problem, when I saw your significantly more elegant solution. Thanks again, for both the speed and simplicity of the answers.

Thanks also for the "Code blocking" html words. Will do.

Chuck6475 · Oct 3, 2012

Okay the following code worked fine for all cases and now doesn't???

Code:

Sub Print_ScoreCards()
  Dim Group   As Integer
    Dim wsPrint As Worksheet
    
    Select Case Sheets("Course").Range("W16").Value
        
    
        Case 5: Set wsPrint = Sheets("Match_Play_Card")
        Case 2: Set wsPrint = Sheets("OrangeBall")
        Case 6: Set wsPrint = Sheets("Team_2_Card")
        Case 7: Set wsPrint = Sheets("Team_4_Card")
        Case 8: Set wsPrint = Sheets("Stroke_NoDots_Card")
        Case Else
            Set wsPrint = Sheets("Stroke_dot_Cards")
    End Select
    
    Application.ScreenUpdating = False
    
    With wsPrint
        For Group = 1 To Sheets("Tee_Times").Range("A1").Value
            .Range("W1").Value = Group
            .Calculate
            .PrintOut Copies:=1
        Next Group
    End With
    
    Application.ScreenUpdating = True
End Sub

--------------------------------------------------------------------

Now I get a "Run-time error '1004'"

Method 'Printout' of object_Worksheet' failed.

--------------------------------------------------------------------

Confused, I tested each options an hour ago and they worked fine. I "protected" the worksheet and when I selected the "radio buttons" I use to generate the value of the scoresheet to print, I got a protection error. I unlocked each radio button in the properties tab and tried it again. No dice either protect or unprotected worksheet.

Sorry, I poster this message about "protection" to the forum in another thread before I found that the printout no longer worked.

Chuck6475 · Oct 3, 2012

Okay, this problem "Run time error 1004" disappears if I "unhide" the worksheets that I print.

Not a major inconvenience but makes for a great deal of tabs.

Macro or other automated method to update and print a worksheet

Chuck6475

Board Regular

Excel Facts

AlphaFrog

MrExcel MVP

Chuck6475

Board Regular

AlphaFrog

MrExcel MVP

Chuck6475

Board Regular

Chuck6475

Board Regular

AlphaFrog

MrExcel MVP

Chuck6475

Board Regular

Chuck6475

Board Regular

Chuck6475

Board Regular

Similar threads

Forum statistics

Share this page

Macro or other automated method to update and print a worksheet

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Board Regular

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock