VBA Schedule Help 2 questions Master Sheet to Sub Sheets

D

Dreamer83187

New Member
Joined
Jan 9, 2016
Messages
1
I need help on coming up with 2 VBA codes for a workbook. This workbook is setup as a working Master Sheet (“Master Staff”) and a lot of sub-worksheets that are setup as another template that is printed day of. There are two problems that I need to get figured out. Problem 1:</SPAN>
Each of the sub sheets were created with specific dates (ie: 04.03,04.05, etc. etc) The master sheet is laid out with Date (E2:GD2), Time (E3:GD3), and Opponent (E4:GD4). See here: </SPAN>
Excel 2010[TABLE="class: grid, width: 500"]
<TBODY>[TR="bgcolor: #dae7f5"]
[TH][/TH]
[TH]D
[/TH]
[TH]E
[/TH]
[TH]F
[/TH]
[TH]G
[/TH]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="bgcolor: #4f81bd, align: center"]GAME DATE
[/TD]
[TD="bgcolor: #4f81bd, align: center"]Sunday, April 03, 2016
[/TD]
[TD="bgcolor: #4f81bd, align: center"]Monday, April 04, 2016
[/TD]
[TD="bgcolor: #4f81bd, align: center"]Tuesday, April 05, 2016
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Game Time
[/TD]
[TD="align: center"]1:05
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]7:05
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Opponent
[/TD]
[TD="align: center"]Cardinals
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]Cardinals
[/TD]
[/TR]
</TBODY>[/TABLE]
MASTER STAFF​
</SPAN>

Right now it is laid out as an entire 6 month calendar with non-game days being noted in the time and opponent categories with an “X”. What I would like to do is filter the days where there is no X into the appropriate sub worksheet template (ie: Game Date on 04.03.16 -</SPAN></SPAN>wksheet 04.03) the information needs to go into the appropriate cell (Date (“Master Staff” E2) à</SPAN></SPAN> “04.03” A2, Time (“Master Staff” E3)à</SPAN></SPAN> “04.03” A3, Opponent (“Master Staff” E4 à</SPAN></SPAN> “04.03” A4). I need this information to weed out the Days that have an “X” in the time and opponent so it does not pull those days to the sub sheets. Here is an example of what the final sub sheet would look like: </SPAN>
Excel 2010[TABLE="class: grid, width: 500"]
<TBODY>[TR="bgcolor: #dae7f5"]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[TH]D
[/TH]
[TH]E
[/TH]
[TH]F
[/TH]
[TH]G
[/TH]
[TH]H
[/TH]
[TH]I
[/TH]
[TH]J
[/TH]
[TH]K
[/TH]
[TH]L
[/TH]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="bgcolor: #000000, align: center"]SUITE ASSIGNMENTS
[/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]<----Date
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]<---Time
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]<---Opponent
[/TD]
[/TR]
</TBODY>[/TABLE]
Game Day​



Second help is to link each of the subsheets to specific days in regards to scheduling. As of right now I have each staff member setup on a specific line and I would like to just input numbers into the “Master Staff” sheet and then update via button on “Master Staff” sheet to a correlating cell on that specific day sheet. See example of “Master Staff” and “04.03”. I need this button to be able to update all sheets when pushed as these schedules will change. The numbers to cells would be as follows: 1-9: A7:A15, 10-18: E7:E15, 19-27: I7:I15</SPAN>

Let me know if this makes sense. Below are cross sections of the sheets:</SPAN>

Master Staff:</SPAN>
Excel 2010[TABLE="class: grid, width: 500"]
<TBODY>[TR="bgcolor: #dae7f5"]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[TH]D
[/TH]
[TH]E
[/TH]
[TH]F
[/TH]
[TH]G
[/TH]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #4f81bd, align: center"]GAME DATE
[/TD]
[TD="bgcolor: #4f81bd, align: center"]Sunday, April 03, 2016
[/TD]
[TD="bgcolor: #4f81bd, align: center"]Monday, April 04, 2016
[/TD]
[TD="bgcolor: #4f81bd, align: center"]Tuesday, April 05, 2016
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Game Time
[/TD]
[TD="align: center"]1:05
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]7:05
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Opponent
[/TD]
[TD="align: center"]Cardinals
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]Cardinals
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="bgcolor: #000000, align: center"]SUITE ATTENDANTS
[/TD]
[TD="bgcolor: #000000, align: center"]SENIORITY DATE
[/TD]
[TD="bgcolor: #000000, align: center"]PAY RATE
[/TD]
[TD="bgcolor: #000000, align: right"][/TD]
[TD="bgcolor: #000000, align: right"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]John Doe 1
[/TD]
[TD="align: right"]1/1/2015
[/TD]
[TD="align: right"]$10.00
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]John Doe 2
[/TD]
[TD="align: right"]1/2/2015
[/TD]
[TD="align: right"]$10.00
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]John Doe 3
[/TD]
[TD="align: right"]1/3/2015
[/TD]
[TD="align: right"]$10.00
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]John Doe 4
[/TD]
[TD="align: right"]1/4/2015
[/TD]
[TD="align: right"]$10.00
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]11
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD]John Doe 5
[/TD]
[TD="align: right"]1/5/2015
[/TD]
[TD="align: right"]$10.00
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]19
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD]John Doe 6
[/TD]
[TD="align: right"]1/6/2015
[/TD]
[TD="align: right"]$10.00
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: right"][/TD]
[/TR]
</TBODY>[/TABLE]
MASTER STAFF​


Excel 2010
ABCDEFGHIJKL
<----Date
<---Time
<---Opponent

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="bgcolor: #000000, align: center"] SUITE ASSIGNMENTS[/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #ffffff, align: center"]LOW SIDE[/TD]
[TD="bgcolor: #ffffff, align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]HIGH SIDE[/TD]
[TD="bgcolor: #ffffff, align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]WSS[/TD]
[TD="bgcolor: #ffffff, align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]John Doe 1 (1)[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]1-4[/TD]
[TD="align: right"][/TD]
[TD="align: center"]John Doe 3 (10)[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]33-36[/TD]
[TD="align: right"][/TD]
[TD="align: center"]John Doe 5 (19)[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]1903[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]John Doe 2 (2)[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]5-8[/TD]
[TD="align: right"][/TD]
[TD="align: center"]John Doe 4 (11)[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]37-39 D[/TD]
[TD="align: right"][/TD]
[TD="align: center"]John Doe 6 (20)[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]1909[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]9-12[/TD]
[TD="align: right"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]40-43[/TD]
[TD="align: right"][/TD]
[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]1925[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]13-16[/TD]
[TD="align: right"][/TD]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]Cambria[/TD]
[TD="align: right"][/TD]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]1927[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]17-20[/TD]
[TD="align: right"][/TD]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]44-49,51-52[/TD]
[TD="align: right"][/TD]
[TD="align: center"]23[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]1960[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]21-24[/TD]
[TD="align: right"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]50[/TD]
[TD="align: right"][/TD]
[TD="align: center"]24[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]1971[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]25-28[/TD]
[TD="align: right"][/TD]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]53-56[/TD]
[TD="align: right"][/TD]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]1979[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]29-31[/TD]
[TD="align: right"][/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]57-60[/TD]
[TD="align: right"][/TD]
[TD="align: center"]26[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]ABC 32[/TD]
[TD="align: right"][/TD]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffffff, align: center"]61-65[/TD]
[TD="align: right"][/TD]
[TD="align: center"]27[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</TBODY>
Game Day​

​
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
You must log in or register to reply here.

Similar threads

R
  • Solved
VBA Help to Automate Year
Replies
2
Views
375
RattlingCarp3048
R
A
  • Question Question
Multiple Criteria Look up: Which Function to use?
Replies
2
Views
212
hagia_sofia
hagia_sofia
D
  • Locked
  • Question Question
VBA to copy only matching data from 2 csv workbooks into the "Master Workbook"
Replies
1
Views
315
RoryA
RoryA
corentint
  • Solved
A recursive procedure
Replies
3
Views
340
John_w
J
G
  • Question Question
Copying date from one sheet to another based on time left
Replies
2
Views
114
Vogateer
V

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top