VBA Staffing advice?

[AndrewNSD]

Hello!

I am having trouble with VBA and getting it to work with the formula I made. I am using it to find data on another sheet by going down a column and finding anyone with a "D" in the row then pulling the first column name and pasting it on another sheet. This is used for staffing and since we have many people it runs well. I would like to improve the Macro since I am going about it in a very different way than how VBA is supposed to be used.

I am having a button macro past my formula into the cells I want it to on another sheet, and then I drag and drop staff into the sheet I have for printing. This is a very roundabout way, but it seems to work for now. I would like to turn that into all VBA the right way. Keep in mind that this is only a small sample of it. I have each sheet for the week that has its own code, and we run on 4 weeks per a schedule. We are always moving people around on this sheet and so it’s hard to have the names just pasted into the printing sheet.

Below is a small mockup of how it would grab the data. I can't figure out how to make the Macro run off a date on the Printing sheet so I ended up with just a bunch of codes per each sheet. *These are not real names of staff*

any information on how to improve this or different ways to make it better would be greatly appreciated. If any of you could somehow point me in the direction of how to get the macro to also know what unit the staff would be working on by the Cell fill color that would be amazing also

for the sheet I made below, we also have the insides of the cells with the letters colored. Each color represents where they are working. I.e. Green means one unit and orange means another and red means they go where the hole is.


any information on how to improve this or different ways to make it better would be greatly appreciated. If any of you could somehow point me in the direction of how to get the macro to also know what unit the staff would be working on by the Cell fill color that would be amazing also

-Key-
D = Days
E = Evenings
N = Nights

---

​

This is the Code Specific to Monday print sheet only

Sub Monday()
Range("K5:K28").Formula = "=IF(ROWS(K$5:K5)>COUNTIF(‘Master’!$D$3:$D$9,""D""),"""",INDEX(‘Master’!$A$3:$A$9,AGGREGATE(15,6,(ROW(‘Master’!$D$3:$D$9)-ROW(‘Master’!$D$3)+1)/( ‘Master’!$D$3:$D$9=""D""),ROWS(K$5:K5))))"
'Values only
Range("J1:Q48").Value = Range("J1:Q48").Value
End Sub

[TABLE="width: 1000, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]8/9
[/TD]
[TD]8/10
[/TD]
[TD]8/11
[/TD]
[TD]8/12
[/TD]
[TD]8/13
[/TD]
[TD]8/14
[/TD]
[TD]8/15
[/TD]
[TD]8/16
[/TD]
[TD]8/17
[/TD]
[TD]8/18
[/TD]
[TD]8/19
[/TD]
[TD]8/20
[/TD]
[TD]8/21
[/TD]
[TD]8/22
[/TD]
[/TR]
[TR]
[TD]NAME
[/TD]
[TD]FTE
[/TD]
[TD]Sunday
[/TD]
[TD]Monday
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[TD]Thursday
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[TD]Sunday
[/TD]
[TD]Monday
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[TD]Thursday
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[/TR]
[TR]
[TD]Crew, Joe
[/TD]
[TD]1.0
[/TD]
[TD]D
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]x
[/TD]
[TD]D
[/TD]
[TD]D
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Dhal, Jane
[/TD]
[TD].8
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]Doe, John
[/TD]
[TD].6
[/TD]
[TD]D
[/TD]
[TD]D
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]D
[/TD]
[TD]D
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]Fredrick, Rick
[/TD]
[TD].7
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]D
[/TD]
[TD]D
[/TD]
[TD]D
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD]Harwell, Phill
[/TD]
[TD].9
[/TD]
[TD]x
[/TD]
[TD]D
[/TD]
[TD]D
[/TD]
[TD]D
[/TD]
[TD]D
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD]Jackson, Andrew
[/TD]
[TD]1.0
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]Olson, Erik
[/TD]
[TD]1.0
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[TD]x
[/TD]
[TD]E
[/TD]
[TD]E
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AndrewNSD]

I forgot to mention that I am running Microsoft Professional, Excel 2010 on Windows 7