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.
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 SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.
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
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.
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.
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.
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.
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 SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.
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
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 nameetc
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.ColumnOnce 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).PrintOutClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.
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.
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
etcand Col C corresponds with Sheet1, D with Sheet2 etc then I can work with that but would prefer that iholten confirms that.
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 selectedIs missing a comma. It should be
cNames = Left(Selection.Address(False, False),1) ' determines the column selectedCan you make that correction and re-try?
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.
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.
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
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
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 SubNow 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 + 1As 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.
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 SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.
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
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.
Thanks again DerbyDad03-- I am home now from work but will try your new improved version monday.
Thanks again
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 SubIn 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.
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.
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 SubNow, 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 SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.
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.
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.
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 SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.
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.
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.
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.
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
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.