Solved I am trying to automate printing schedules at work.

January 4, 2013 at 07:08:29
Specs: Windows 7
I have a list of names of people on a crew on one sheet of my workbook. On another sheet is the schedule. I want to be able, using a command button, print schedules one at a time with the selected workers name at the top of it. If I highlight three workers and then push the command button I want to print three schedules - each one having the selected worker's name at the top of it.

See More: I am trying to automate printing schedules at work.

Report •


✔ Best Answer
January 4, 2013 at 12:51:07
This code, which has no error checking or anything exotic, will do what you ask.

That fact that the names in Column C (3) get assigned to Sheet 3, Column D (4) to Sheet 4, etc. makes choosing the Sheet for each name fairly simple.

The one thing that might be a bit inconvenient is that Excel loops through a selected range the way most of us read, from left to right. e.g. C1, D1, E1, C2, D2, E2, etc. Therefore your sheets won't print out in crew order, they will print out in a non-collated manner.

That is certainly fixable but it will require more code, which AlteK has probably already written. ;-)

An advantage is that you can select one name, all names or random, non-contiguous names if you want and print sheets for just those names.

As I said, this code is very simple but it works, and it also shows some of the inner workings of Excel and VBA.

Sub PrintSchedules()
 For Each lName In Selection
'Match Sheet number to Column Number of each lName
    shtNum = lName.Column
'Place name in C3 of proper sheet
    Sheets(shtNum).Range("C3") = lName
'Print out the sheet
    Sheets(shtNum).PrintOut
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
January 4, 2013 at 09:07:34
Hi

Here is a draft example of how you might do this. You'll need to set up your print parameters in advance and change the sheet names where appropriate - in this macro sheet2 contains the schedule. Also, printing is environment dependant so give this a try and come back with anything that doesn't work in your environment.

Sub PriScheds()
nNames = Selection.Count 'Captures the number of names highlighted
lNames = Selection  'Captures the names highlighted in Sheet1

Sheets("Sheet2").Select

For i = 1 To nNames
        With ActiveSheet.PageSetup
            If nNames = 1 Then
                    .LeftHeader = lNames   'Sets the name as the header
            Else
                    .LeftHeader = lNames(i, 1)   'Sets the name as the header
            End If
        End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
Next

End Sub


Report •

#2
January 4, 2013 at 10:03:23
You might want to add this line before the End Sub just for a little clean up. That way you won't have the last name selected set as the Header should you want to print a generic copy of the schedule.

ActiveSheet.PageSetup.LeftHeader = ""

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
January 4, 2013 at 10:30:25
Hi DerbyDad

Hope you had a great holiday season.

I agree with your suggestion. In fact, there are quite a few operations that should be added i.e. checking that the cell selections are in the correct range and error handling should the selection be outside the correct range.

Of course, we'd need more specific information about the spreadsheets to do this sort of thing. What I presented is fairly generic and easily adapted so that it is a starting point for the OP.


Report •

Related Solutions

#4
January 4, 2013 at 11:58:09
I'm sure you know that my comment was not intended to imply that your suggested macro was lacking in any way.

The only reason I posted that suggestion was that I played with your macro in a workbook that I had open and was quite pleased with the way it worked. A few minutes later I printed out a sheet from that workbook for actual work purposes and the last header used by the macro was still there. Since I had to go in and manually delete it, it prompted me to suggest that the clean up might be useful to have as the last task of the macro.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
January 4, 2013 at 12:14:27
Thanks Altek,

Thanks for the info. I'll try that but I'll have to modify the code somewhat. I wasnt specific enough in my post. On the sheet with the workers names I have the different crews listed in different columns. Each crew uses a different schedule on a different sheet. I have a cell "C3" on each sheet reserved to have the names pasted before I print so I won't be using the "leftheader" code. Can I just paste as I step through the selection for each column. What I would prefer is to have one command button. Each name selected in column "C" will use sheet 3 schedule and each name selected in column "D" will use sheet 4 schedule and so on. I could have a separate command button for each column (crew) to simplify things, I guess.


Report •

#6
January 4, 2013 at 12:51:07
✔ Best Answer
This code, which has no error checking or anything exotic, will do what you ask.

That fact that the names in Column C (3) get assigned to Sheet 3, Column D (4) to Sheet 4, etc. makes choosing the Sheet for each name fairly simple.

The one thing that might be a bit inconvenient is that Excel loops through a selected range the way most of us read, from left to right. e.g. C1, D1, E1, C2, D2, E2, etc. Therefore your sheets won't print out in crew order, they will print out in a non-collated manner.

That is certainly fixable but it will require more code, which AlteK has probably already written. ;-)

An advantage is that you can select one name, all names or random, non-contiguous names if you want and print sheets for just those names.

As I said, this code is very simple but it works, and it also shows some of the inner workings of Excel and VBA.

Sub PrintSchedules()
 For Each lName In Selection
'Match Sheet number to Column Number of each lName
    shtNum = lName.Column
'Place name in C3 of proper sheet
    Sheets(shtNum).Range("C3") = lName
'Print out the sheet
    Sheets(shtNum).PrintOut
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
January 4, 2013 at 12:51:53
try this - assumes that names in a single column is selected. Will look into multiple column/names selection

Sub PriScheds()

nNames = Selection.Count 'Captures the number of names highlighted
lNames = Selection  'captures the names highlighted in Sheet1
cNames = Left(Selection.Address(False, False), 1) ' determines the column selected


'Determines the schedule sheet based on the Column selected - replace sheet names and columns as required

If cNames = "C" Then
    Sheets("Sheet2").Select
ElseIf cNames = "D" Then
    Sheets("Sheet3").Select
ElseIf cNames = "E" Then
    Sheets("Sheet4").Select
End If


For i = 1 To nNames
        With ActiveSheet.PageSetup
            If nNames = 1 Then
                    Range("C3") = lNames   'Sets the name 
            Else
                    Range("C3") = lNames(i, 1)   'Sets the name
            End If
        End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
Next

End Sub


Report •

#8
January 4, 2013 at 13:48:45
Is there anything in the Names spreadsheet that points to the schedule to be used? For example, could the row header for each Crew be the name of the corresponding schedule worksheet?

Might be worthwhile to post a section of your names list and describe how you are likely to make selections e.g.
usually all names for a single crew
All names for all crews
a subset of names across all crews or if you're doing a subset would it only be from a single crew
a single name

etc


Report •

#9
January 4, 2013 at 14:10:34
re: Is there anything in the Names spreadsheet that points to the schedule to be used?

Didn't the OP address that in Response #5 when (s)he said:

Each name selected in column "C" will use sheet 3 schedule and each name selected in column "D" will use sheet 4 schedule and so on.

That's why the code I posted is so simple. It uses the Column property (which returns the number of the Column) to determine the Column of each name in the selected range and sets the shtNum variable to that value:

 For Each lName In Selection
'Match Sheet number to Column Number of each lName
    shtNum = lName.Column

Once the shtNum variable is set, the codes uses that value to refer to that sheet:

'Place name in C3 of proper sheet
    Sheets(shtNum).Range("C3") = lName
'Print out the sheet
    Sheets(shtNum).PrintOut

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
January 4, 2013 at 14:22:42
Great stuff Altek

Here is the modified code:


Private Sub CommandButton1_Click()

  nNames = Selection.Count 'Captures the number of names highlighted

lNames = Selection  'captures the names highlighted in Sheet1

cNames = Left(Selection.Address(False, False)1) ' determines the column selected

 'Determines the schedule sheet based on the Column selected - replace sheet names and columns as required

 If cNames = "C" Then
   Sheets("Yeager").Select

ElseIf cNames = "D" Then
   Sheets("Forster").Select

ElseIf cNames = "E" Then
   Sheets("Errett").Select

ElseIf cNames = "F" Then

    Sheets("Helterbran").Select

ElseIf cNames = "G" Then

    Sheets("Gallegos").Select

ElseIf cNames = "H" Then

    Sheets("Baumgartner").Select

End If

 For i = 1 To nNames

        With ActiveSheet.PageSetup

            If nNames = 1 Then

                    Range("C3") = lNames   'Sets the name

            Else

                    Range("C3") = lNames(i, 1)   'Sets the name

            End If

        End With

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _

        IgnorePrintAreas:=False

Next


End Sub

+++++++++++++++++++
Some things work, some don't. When selecting just one name from any crew and pushing the command button the proper schedule prints (the one associated with the crew column) but it prints without the name pasted in C3. The name is actually pasted in C3 on the same sheet with the names on it. My crew members names start on row 20.

If I select more than one name from the same column I get a run error. The program stops at :

Range("C3") = lNames(i, 1) 'Sets the name

Don't know how to fix.

To answer your questions-- The association with each column to the appropriate spreadsheet seems to be working.


Report •

#11
January 4, 2013 at 14:30:50
I took that to be indicative rather than precise descriptions of sheet names. If, as you believe, the schedule sheet names are

Sheet1
Sheet2
Sheet3
etc

and Col C corresponds with Sheet1, D with Sheet2 etc then I can work with that but would prefer that iholten confirms that.


Report •

#12
January 4, 2013 at 14:44:50
Hi iholten

not sure if there was a mess up in the copy and paste of your code but this line

cNames = Left(Selection.Address(False, False)1) ' determines the column selected

Is missing a comma. It should be

cNames = Left(Selection.Address(False, False),1) ' determines the column selected

Can you make that correction and re-try?


Report •

#13
January 4, 2013 at 14:49:05
Iholten,

Did you try the code I posted in Response #6?

If, as you said, the names in Column C are for the schedule on Sheet 3, Column D for Sheet 4, etc. then the code to do what you want is fairly simple.

Since Column C is known in VBA as Column 3, matching the Column number of each selected cell to the Sheet number is quite easy:

For every lName in Column C, lName.Column = 3. For every lName in Column D, lName.Column = 4

Since sheets can be referred to by number in VBA, e.g. Sheet(3), we don't need the actual sheet names. If we assign the Column number to a sheet number variable, we're all set:

shtNum = lName.Column

Sheets(shtNum) will refer to whichever sheet is the same number as the Column for the current lName.

Again, this assumes that what you said in Response #5 is accurate.

"Each name selected in column "C" will use sheet 3 schedule and each name selected in column "D" will use sheet 4 schedule and so on."

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
January 4, 2013 at 15:08:59
To Altek:

The comma disappeared only when I edited my reply to you. It is in my code.
That particular code is working because the correct spreadsheet is always selected. It's just the pasting of the name into my spreadsheet that doesn't work. And also if I select multiple names it doesn't run at all and stops at the before mentioned code:
Range("C3") = lNames(i, 1)
Additionally, when my schedule prints without a name at the top the name is inserted into cell C3 on my names sheet.


Report •

#15
January 4, 2013 at 15:10:16
To DerbyDad03:

I haven't tried your code yet but will. I don't know if Column D is associated with sheet 4. Excel assigns sheet numbers in the order that they are created doesn't it?

Thank you


Report •

#16
January 4, 2013 at 15:47:04
To DerbyDad03:

Just tried your code and it appears to work flawlessly. Brilliantly simple. Thank you. All I had to do is rearrange my sheets a little. It appears to work in all cases so far. With just one name selected, with many selected from the came crew,(column) or with many selected from all the columns(crews). It appears to work flawlessly. Now I need to study it and see why it works. I am just a novice at this particular language. I used tp program in autolisp a lot. I am very impressed by how much so little code does so effortlessly. You folks are very impressive.

Thank you very much


Report •

#17
January 4, 2013 at 16:04:38
Excel may assign numbers in the sheet name in the order they are created, but we are referring to the sheets in VBA and that changes things. I am not using the Excel sheet name in my code.

Sheet3 as a tab name is not the same as Sheets(3) in VBA.

In VBA Sheets("Sheet3") will refer to the sheet with the tab name Sheet3, but Sheets(3) will refer to the 3rd tab, regardless of it's name or how it got into the 3rd position. Sheets(3) will always refer to the 3rd sheet, assuming it exists. If only 2 sheets exists (even if one is named Sheet3) and you try to access Sheets(3) via VBA you'll get a Subscript Out Of Range error.

Open a new spreadsheet and run this code. You should see the sheet names as Sheet1, Sheet2, Sheet3

Sub SheetNumber()
  For shtNum = 1 To Sheets.Count
   MsgBox "shtNum = " & shtNum & vbCrLf & Sheets(shtNum).Name
  Next
End Sub

Now insert some sheets, move sheets around, change their names, whatever.

Now run the code again and you'll see the Sheet Names appear in the order that the tabs read, from left to right.

So, if your Column C names really are assigned to the schedule on the third tab, known to VBA as Sheets(3), then we can use the Column number to refer to Sheets(3). If Column C actually refers to the fourth sheet, Column D to the fifth, etc. then just do some simple math such as:

shtNum = lName.Column + 1

As long as the columns and schedule sheets follow a sequential pattern (which you could easily set up) then the simple code I posted should work.

Again, the only inconvenience is that it won't keep the crews together since it will print out a sheet from Column C, then D then E then C then D then E, etc. That might make it just a little harder to hand them out to say a crew chief, since you'll need to sort them by crew after printing.

If the code works as written, let me know. I might play around and see what can be done about the order of printing.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#18
January 4, 2013 at 16:27:16
Try this version...it should keep your crew printouts together by forcing the code to print on a Column by Column basis instead of from left to right.

I use Column 3 (C) as the start column and 8 (H) as the end. Modify if required.

Sub PrintSchedules()
'Loop through each Column of names
  For crewCol = 3 To 8 
'Loop through each Selected name in current Column
   For Each lName In Selection
'If the current Selection is in the Current column...
     If lName.Column = crewCol Then
'Place name in C3 of proper sheet
        Sheets(crewCol).Range("C3") = lName
'Print out the sheet
        Sheets(crewCol).PrintOut 'preview:=True
     End If
   Next
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
January 4, 2013 at 16:34:00
To DerbyDad03;

As I posted earlier it works perfectly. I just had to arrange the sheets a little bit. I see that the sheet numbers are assigned in order by tab number. Thanks for the information. As far as printing out of order as far as crews go - I never considered that a problem. The crews are small. A max of 5 per crew. I'm still amazed at how concise and powerful those few lines of code are. This must not be your first day of programming. If you would like to try to print each crew together that is up to you. It is not necessary to me but if you consider it a challenge, I will be listening.

Thanks again DerbyDad03
and thanks to Altek also for your help


Report •

#20
January 4, 2013 at 16:46:31
The Crew by Crew version is posted in Response # 18.

It not as efficient as the original version because it loops through the selected cells multiple times, but if you'll only have a maximum of 40 selected cells, the printer will still be pumping out sheets way after the code is done.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#21
January 4, 2013 at 17:27:17
Thanks again DerbyDad03--

I am home now from work but will try your new improved version monday.
Thanks again


Report •

#22
January 5, 2013 at 07:27:16
What is interesting is the way that VBA reads through a selected range of cells.

The code below shows the differences based on how the cells were selected.

Open a new workbook for this demonstration.

Let's say the entire range to be selected is A1:C3. Select the entire range by clicking in A1 and dragging to C3. Then run this code.

Sub SelectionOrder()
'Loop through Selection and build temp Address list
 For Each cell In Selection
  tempList = tempList & cell.Address & ", "
 Next
'Determne next empty row in Column E
   lastRw = Range("E" & Rows.Count).End(xlUp).Row + 1
'Strip off extra comma from tempOrder string
   finalList = Left(tempList, Len(tempList) - 2)
'Place final Address list in Column E
   Range("E" & lastRw) = finalList
End Sub

In E1 you should see a list that shows that VBA read through the selected range from left to right, then down, then left to right, then down, etc:

$A$1, $B$1, $C$1, $A$2, $B$2, $C$2, $A$3, $B$3, $C$3

Now select the following range, in the order given by using Ctrl-Click in each cell and then run the code.

C1, B1, A1, C3, A3

If the selection was read from left to right then down as was done when the entire range was selected, you would expect to see this output:

$A$1, $B$1, $C$1, $A$3, $C$3

Instead, you will see a list that is in the same order it was selected:

$C$1, $B$1, $A$1, $C$3, $A$3

Now, select that same range by clicking in C1, dragging back to A1, Ctrl-Click C3, Ctrl-Click A3.

Since VBA reads the cells in the order that they were selected, you would expect to see this, right?

$C$1, $B$1, $A$1, $C$3, $A$3

Instead, you see this:

$A$1, $B$1, $C$1, $C$3, $A$3

As you can see, the order in which the selected range is read is determined both by the order in which the cells were selected as well as how they were selected. The drag method always results in VBA reading from left to right, down, left to right, down etc., regardless of which direction you dragged to create the selection. Ctrl-Clicked cells are read in the order in which they were Ctrl-Clicked.

Since we can never tell how those darn users will select cells, we sometimes have to compensate for the differences in how VBA will read through the selection. If we want the cells used (as opposed to read) in a specific order, we may need to add additional code to make that happen. The method I used in Response #18 is one way to ensure that the selected cells are used in a predictable order regardless of how they were selected.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#23
January 5, 2013 at 08:28:42
Good Morning;

Interesting stuff. I can't try that I don't believe until I get back to work. I don't think my version of Excel has development tools. Not sure yet. Will look.On a side note, my schedule program creates 6 different schedules for any year and outlines all of the holidays that we have. One of our holidays is Good Friday and the formula for that is very involved. Check that out when you get bored.


Report •

#24
January 5, 2013 at 08:41:00
This was just for fun and to give you some more code to study. (We could go on forever!)

Let's say you need to deal with vacations or sick days and need to move a crew member to a different crew for a day or two.

This code will allow you select a crew member from any crew and print the schedule from any sheet with the selected name in C3.

The first piece of code builds a Data Validation drop down in B1 that contains all of the Sheet Names for the schedule sheets, sheets 3 through 8. Run it once to build the Data Validation list. Obviously you could do this manually, but this is a lesson in VBA, isn't it? ;-)

Sub CreateDropDownOfSheets()
'Loop through sheets and build string of Sheet Names
  For shtNum = 3 To 8
    shtList = shtList & Sheets(shtNum).Name & ", "
  Next
'Place Data Validation list in B1
     With Range("B1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=shtList
     End With
End Sub

Now, select a schedule sheet from the drop down, then select any crew member's name, then run the following code. (Remove the single quote from in front of preview to Print Preview the sheet instead of actually printing it)

Sub OneOffSchedule()
'Place selected Crew name in the Sheet named in B1
        Sheets(Range("B1").Value).Range("C3") = Selection
'Print out the sheet named in B1
        Sheets(Range("B1").Value).PrintOut 'preview:=True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#25
January 5, 2013 at 08:51:42
re: " I don't think my version of Excel has development tools."

Except for some specific versions of Excel for Mac, I believe that all versions of Excel (especially for Windows) come with VBA installed. What version are you running?

re: "One of our holidays is Good Friday and the formula for that is very involved. Check that out when you get bored."

I'm confused...check what out?

BTW...you might want to review this How To for some VBA troubleshooting techniques. I wrote it years ago after finding out that using these techniques not only helped me troubleshoot my own code, but were really valuable when trying to reverse engineer code that other authors had written.

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#26
January 5, 2013 at 09:44:33
One more question. Do I need some code that will prevent a schedule printing for a selected cell that has no name in it? My name lists are of different lengths and if I want to easily select everyone I would select the whole range - say C20 to H26. In that group will be several blank cells.

re: "I'm confused...check what out?"

The formula that determines which day and month Good Friday falls on each year. Most holidays follow a simple formula - last monday in May, first Monday in Sep, etc Not so easy for Good Friday. Easter is the first Sunday following the first full moon following the spring solstice (Mar21) and of course Good Friday is two days before that. Formula available on the internet - was very involved.


Report •

#27
January 5, 2013 at 10:58:07
As far as the formula for Good Friday being "very involved" - not that I know what that means since you didn't post the formula or a link - why does that matter? If somebody else already solved the issue, why not use their solution?

As far as not printing sheets with no name, a simple If - Then to check that the selected cell is not empty solves that issue:

Sub PrintSchedules()
'Loop through each Column of names
  For crewCol = 3 To 8 'Set your own start and end column
'Loop through each Selection name in Column, skip if blank
   For Each lName In Selection
    If lName <> "" Then '<<<<<<<<<<<<<<
'If the current Selection is in the Current column...
     If lName.Column = crewCol Then
'Place name in C3 of proper sheet
        Sheets(crewCol).Range("C3") = lName
'Print out the sheet
        Sheets(crewCol).PrintOut preview:=True
     End If
    End If
   Next
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#28
January 5, 2013 at 11:06:00
Since this thread is morphing into a different question - i.e. "How to determine Good Friday date" I'll ask that you post that question as a new thread. I found an answer that seems fairly straight forward, so if you'll ask that question in it's own thread, I'll offer a suggestion.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#29
January 5, 2013 at 18:20:43
Thanks again for another solution. Misunderstanding on the good friday issue. I never needed help with a solution. I already found that and am using it successfully in my spreadsheet. I just thought you may be interested in the complexity of the formulas used in determining which day good friday is for any given year.

Report •

#30
January 5, 2013 at 18:23:54
Yet you still haven't shared the formula, so how could I be "interested in the complexity" if I don't even know what formula you are referring to?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#31
January 5, 2013 at 18:36:18
Public Function EasterUSNO(YYYY As Long) As Long
    Dim C As Long
    Dim N As Long
    Dim K As Long
    Dim I As Long
    Dim J As Long
    Dim L As Long
    Dim M As Long
    Dim D As Long
    
    C = YYYY \ 100
    N = YYYY - 19 * (YYYY \ 19)
    K = (C - 17) \ 25
    I = C - C \ 4 - (C - K) \ 3 + 19 * N + 15
    I = I - 30 * (I \ 30)
    I = I - (I \ 28) * (1 - (I \ 28) * (29 \ (I + 1)) * ((21 - N) \ 11))
    J = YYYY + YYYY \ 4 + I + 2 - C + C \ 4
    J = J - 7 * (J \ 7)
    L = I - J
    M = 3 + (L + 40) \ 44
    D = L + 28 - 31 * (M \ 4)
    EasterUSNO = DateSerial(YYYY, M, D)
End Function


Report •

#32
January 5, 2013 at 20:34:41
I guess you could call that complex. if you want to use a formula instead of a UDF, try
this.

This formula will return the date for Easter in the year represented by YYYY. It was written by Chip Pearson and found at:

http://www.cpearson.com/excel/Easte...

=FLOOR("5/"&DAY(MINUTE(YYYY/38)/2+56)&"/"&YYYY,7)-34

YYYY could be a hard coded four digit year, or a cell reference to a four digit year, or a function that determines the year of a given date such as YEAR(TODAY()).

Since this will return the Date for Easter, just subtract 2 from it to have it return the date of Good Friday:

=FLOOR("5/"&DAY(MINUTE(YYYY/38)/2+56)&"/"&YYYY,7)-34-2

or

=FLOOR("5/"&DAY(MINUTE(YYYY/38)/2+56)&"/"&YYYY,7)-36

Note: Per the Website, the formula is only reliable for years between between 1900 and 2368.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question