Help with Conditional Option and Loop Routine for Print Macro

[trandle]

I am seeking help to enhance the existing “PrintScorecard” macro in the attached sample workbook with a conditional routine that evaluates the “Course” and number of “Tee Times” found in cells D4 and D5 of the Weekly_Scoreboard worksheet. The above macro has been assigned to the Print Scorecards command button on the Weekly_Scoreboard worksheet.

I would like for the macro to determine which course scorecard template worksheet to copy data from based upon the contents of Weekly_Scoreboard!D4. There are two worksheets named New Course Scorecard Template and Old Course Scorecard Template to which I am expecting the macro to copy names and handicaps to certain fields in each respective template.

In addition, I would like the macro to perform a loop of the copy subroutine based upon the value in Weekly_Scoreboard!D5 which contains a value that represents the number of tee times, or groupings, for this game. The first pass of the copy loop would be to copy the player names and handicaps to each respective scorecard template for players 1-4. The second pass of the copy loop would copy the data for players 5-8 and the third pass to copy data for players 9-12, and so on based upon the value in Weekly_Scoreboard!D5.

The source data for the copy subroutine comes from the Lists & Tables worksheet and may be follow the cell addresses contained within the PrintScorecard macro assigned to the Weekly_Scoreboard worksheet.
Obviously I used the macro recorder to create the PrintScorecard macro so I doubt it’s the most efficient way to handle this request, so I am open to suggestions on how to accomplish what I want and improve upon the macro where possible. Thank you in advance for any help or assistance you can offer!

[skywriter]

This code is getting the player information from the weekly scorecard sheet, it was easier for me to do it that way.

Check the format of your date and tee time cells on your new course sheet. Make sure they are date and time. I just opened the file and it appears they aren't. I only ran this code on the old course sheet since they are identical and just now noticed the problem.

[trandle]

skywriter - thanks for your help. Your solution works perfectly, met my specficiations and greatly exceeded them in efficient and performance, especially over the silly macro I initially created using the macro recorder. I need to move the code into another workbook (with some minor modification for cell addresses and worksheet names, etc.). Upon doing so, the code appears to have been recognized as a module for the new workbook however, I am unable to run the code as it appears the new workbook is not recognizing the macro. I've assigned the code to a simliar command button in the new workboook and nothing is happening. I've tried to step into the code using F8 and still nothing. Perhaps there is a step or two I haven't performed?

Also, I want to add additional code after the "next c" line to clear the contents of the respective score card template once the End With loop has completed. Wouild I add code that selects the cells where values were pasted and then use "Select.ClearContents" command to erase after the "next c" command?

[skywriter]

First of all thank you for the rep points, much appreciated.
If you want to post your new sheet I'd be happy to look at it and help you. Second of all there was nothing wrong with your macro. I'll give your some pointers to help you figure out the issue, but again I'd be more than happy to help you and I'll even tell you why you had an issue after I figure it out. Your call.

You have Excel 2010 the same version as me which is good.
First your spreadsheet must be saved as a .xlsm macro enabled version.
If you are in the VBE, visual basic editor and you see the module under modules, and the workbooks is save as a .xlsm then you are on the right track.
One thing macros do that slows them down is they put in a lot of unnecessary code. All that select this sheet and select that sheet stuff is unnecessary and slow. What you need to do is specifically refer to a sheet and a cell on that sheet and that's it. I can say make this cell on this sheet be the value of that cell on that sheet and Excel does it. With the macro is selects a sheet, selects a cell, copies the cell, selects the other sheet, selects the other cell pastes the value, then does it over and over.

If you look in that same window where you see the little folder icon with the - symbol or the + if it's not expanded, you will see another one above it that says Microsoft Excel Objects. If it's not expanded, expand it. I'm looking at your original folder and you had 4 sheets. If you look at them you will notice for instance that for the Weekly_Scoreboard sheet, there's that name in (), but to the left of it, it says Sheet1. To Excel, this is the real name of that sheet. Now you can change this name, but you have to know how. The beauty of this name is I can use it directly in code and it doesn't matter if anybody changes the TAB name of the sheet, my code will still refers to this sheet.
If I refer to this sheet in code with the TAB name it looks like this.
Dim ws as Worksheet.
Set ws = Worksheets("Weekly_Scoreboard")
Now I can refer to this and if I type ws. then Excel helps me by as soon as I type the period after ws I get a whole lot of stuff to choose from. However if someone changes the TAB name of the sheet now ws is a problem.
Look at my code, early in the code I have a line that says Sheet1.Range("D4").Value so that code refers specifically to cell D4 on the Weekly_Scoreboard sheet. Change the TAB name it still works, but put it into a new workbook where a Sheet1, remember the name on the left, not the TAB name, is blank sheet or doesn't have data in the right places and nothing works right.
Now that you know this you can follow the code better.

In a nutshell the code does this.
Dim is a statement to declare variables and you can see the type of information they hold by the Dim nameofvariable as Long, the Long is the type and that's a long integer type. Range is just what it sounds like a cell or group of cells.
So I look in your dropdown list and see if you chose OldCourse if so it sets template and you see how that works. Then I get the value you chose for number of tee times.
Now you get to a line that says Worksheets(TemplateName), now is where you might have an issue, because this refers to a name you could have changed, meaning the TAB names of your template sheets. I did not refer to this one with the real name of the sheet like I did earlier.
All the stuff below With Worksheets(TemplateName) is looping through your player names, handicaps etc. that were on the main sheet. Everything refers to a specific cell. Your templates were identical with the boxes where the data needed to be in exactly the same cell on each sheet. I used some trickery to get the player names etc. and all of those were in specific cells and spaced evenly apart so I was able to loop based on your number of tee times. If things are in different cells or have more spaces than before, there will be problems. If names of sheets have added, more sheets were added etc. There will be problems, this code was highly customized to the sheet you posted.

As far as clearing the contents, do it after all the other code, just before the End Sub line. Refer to the specific sheet and the cells you want to to clear, not just the whole sheet because any headers you have will be gone also. Make sure you use ClearContents otherwise your formatting will be gone.

If you still have issues post your sheet and I will look at it. If you get it mostly figure out and still have a snag, let me know, I'm here to help.

Besides I used to be a golfer. I have a bag full of Ping's that haven't seen grass in a few years.

Good Luck!!!

[trandle]

Thanks for your insight on VBA. It is fascinating to me and I'm trying to pick it up. Once someone on this forum explained to me that learning VBA can be a bit like trying to drink from a fire hose. I will check on the items you mentioned and follow up to let you know I've gotten the code to run successfully in the new workbook. Thanks also for the tips on clearing the contents.