Hello,
I create a team tracking sheet where everyone enters their numbers every month. Now, I need to create a macro that will rename the sheets automatically based a range of cells. Instead of renaming the tabs one by one. The values are dates which I want the tabs formatted with "mm.dd". I also need the macro to read the range of cells that have dates for everyday of the month excluding weekends and holidays.
I have attached the spreadsheet that is created and I need the tabs to have the dates from worksheet Daily Totals from A4:A27. And if there are left over worksheets, it needs to be hidden.
I create a team tracking sheet where everyone enters their numbers every month. Now, I need to create a macro that will rename the sheets automatically based a range of cells. Instead of renaming the tabs one by one. The values are dates which I want the tabs formatted with "mm.dd". I also need the macro to read the range of cells that have dates for everyday of the month excluding weekends and holidays.
I have attached the spreadsheet that is created and I need the tabs to have the dates from worksheet Daily Totals from A4:A27. And if there are left over worksheets, it needs to be hidden.
Team tracking master.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
3 | Date | RFA FUNDING | Delayed Invoice Flooring | Commitments | Invoice Prep | Next Day | Transfers | Extensions | Total Email Count | ||
4 | 9/1/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
5 | 9/2/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
6 | 9/3/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
7 | 9/7/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
8 | 9/8/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
9 | 9/9/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
10 | 9/10/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
11 | 9/13/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
12 | 9/14/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
13 | 9/15/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
14 | 9/16/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
15 | 9/17/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
16 | 9/20/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
17 | 9/21/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
18 | 9/22/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
19 | 9/23/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
20 | 9/24/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
21 | 9/27/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
22 | 9/28/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
23 | 9/29/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
24 | 9/30/2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
25 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||
26 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||
27 | |||||||||||
28 | TOTALS: | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
Daily Totals |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B4 | B4 | =Sheet1!B48 |
C4 | C4 | =Sheet1!B46 |
D4 | D4 | =Sheet1!B47 |
E4 | E4 | =Sheet1!B45 |
F4 | F4 | =Sheet1!B51 |
G4 | G4 | =Sheet1!B54 |
H4 | H4 | =Sheet1!B53 |
I4 | I4 | =Sheet1!B26 |
B5 | B5 | =Sheet2!B48 |
C5 | C5 | =Sheet2!B46 |
D5 | D5 | =Sheet2!B47 |
E5 | E5 | =Sheet2!B45 |
F5 | F5 | =Sheet2!B51 |
G5 | G5 | =Sheet2!B54 |
H5 | H5 | =Sheet2!B53 |
I5 | I5 | =Sheet2!B26 |
B6 | B6 | =Sheet3!B48 |
C6 | C6 | =Sheet3!B46 |
D6 | D6 | =Sheet3!B47 |
E6 | E6 | =Sheet3!B45 |
F6 | F6 | =Sheet3!B51 |
G6 | G6 | =Sheet3!B54 |
H6 | H6 | =Sheet3!B53 |
I6 | I6 | =Sheet3!B26 |
B7 | B7 | =Sheet4!B48 |
C7 | C7 | =Sheet4!B46 |
D7 | D7 | =Sheet4!B47 |
E7 | E7 | =Sheet4!B45 |
F7 | F7 | =Sheet4!B51 |
G7 | G7 | =Sheet4!B54 |
H7 | H7 | =Sheet4!B53 |
I7 | I7 | =Sheet4!B26 |
B8 | B8 | =Sheet5!B48 |
C8 | C8 | =Sheet5!B46 |
D8 | D8 | =Sheet5!B47 |
E8 | E8 | =Sheet5!B45 |
F8 | F8 | =Sheet5!B51 |
G8 | G8 | =Sheet5!B54 |
H8 | H8 | =Sheet5!B53 |
I8 | I8 | =Sheet5!B26 |
B9 | B9 | =Sheet6!B48 |
C9 | C9 | =Sheet6!B46 |
D9 | D9 | =Sheet6!B47 |
E9 | E9 | =Sheet6!B45 |
F9 | F9 | =Sheet6!B51 |
G9 | G9 | =Sheet6!B54 |
H9 | H9 | =Sheet6!B53 |
I9 | I9 | =Sheet6!B26 |
B10 | B10 | =Sheet7!B48 |
C10 | C10 | =Sheet7!B46 |
D10 | D10 | =Sheet7!B47 |
E10 | E10 | =Sheet7!B45 |
F10 | F10 | =Sheet7!B51 |
G10 | G10 | =Sheet7!B54 |
H10 | H10 | =Sheet7!B53 |
I10 | I10 | =Sheet7!B26 |
B11 | B11 | =Sheet8!B48 |
C11 | C11 | =Sheet8!B46 |
D11 | D11 | =Sheet8!B47 |
E11 | E11 | =Sheet8!B45 |
F11 | F11 | =Sheet8!B51 |
G11 | G11 | =Sheet8!B54 |
H11 | H11 | =Sheet8!B53 |
I11 | I11 | =Sheet8!B26 |
B12 | B12 | =Sheet9!B48 |
C12 | C12 | =Sheet9!B46 |
D12 | D12 | =Sheet9!B47 |
E12 | E12 | =Sheet9!B45 |
F12 | F12 | =Sheet9!B51 |
G12 | G12 | =Sheet9!B54 |
H12 | H12 | =Sheet9!B53 |
I12 | I12 | =Sheet9!B26 |
B13 | B13 | =Shee10!B48 |
C13 | C13 | =Shee10!B46 |
D13 | D13 | =Shee10!B47 |
E13 | E13 | =Shee10!B45 |
F13 | F13 | =Shee10!B51 |
G13 | G13 | =Shee10!B54 |
H13 | H13 | =Shee10!B53 |
I13 | I13 | =Shee10!B26 |
B14 | B14 | =Sheet11!B48 |
C14 | C14 | =Sheet11!B46 |
D14 | D14 | =Sheet11!B47 |
E14 | E14 | =Sheet11!B45 |
F14 | F14 | =Sheet11!B51 |
G14 | G14 | =Sheet11!B54 |
H14 | H14 | =Sheet11!B53 |
I14 | I14 | =Sheet11!B26 |
B15 | B15 | =Sheet12!B48 |
C15 | C15 | =Sheet12!B46 |
D15 | D15 | =Sheet12!B47 |
E15 | E15 | =Sheet12!B45 |
F15 | F15 | =Sheet12!B51 |
G15 | G15 | =Sheet12!B54 |
H15 | H15 | =Sheet12!B53 |
I15 | I15 | =Sheet12!B26 |
B16 | B16 | =Sheet13!B48 |
C16 | C16 | =Sheet13!B46 |
D16 | D16 | =Sheet13!B47 |
E16 | E16 | =Sheet13!B45 |
F16 | F16 | =Sheet13!B51 |
G16 | G16 | =Sheet13!B54 |
H16 | H16 | =Sheet13!B53 |
I16 | I16 | =Sheet13!B26 |
B17 | B17 | =Sheet14!B48 |
C17 | C17 | =Sheet14!B46 |
D17 | D17 | =Sheet14!B47 |
E17 | E17 | =Sheet14!B45 |
F17 | F17 | =Sheet14!B51 |
G17 | G17 | =Sheet14!B54 |
H17 | H17 | =Sheet14!B53 |
I17 | I17 | =Sheet14!B26 |
B18 | B18 | =Sheet15!B48 |
C18 | C18 | =Sheet15!B46 |
D18 | D18 | =Sheet15!B47 |
E18 | E18 | =Sheet15!B45 |
F18 | F18 | =Sheet15!B51 |
G18 | G18 | =Sheet15!B54 |
H18 | H18 | =Sheet15!B53 |
I18 | I18 | =Sheet15!B26 |
B19 | B19 | =Sheet16!B48 |
C19 | C19 | =Sheet16!B46 |
D19 | D19 | =Sheet16!B47 |
E19 | E19 | =Sheet16!B45 |
F19 | F19 | =Sheet16!B51 |
G19 | G19 | =Sheet16!B54 |
H19 | H19 | =Sheet16!B53 |
I19 | I19 | =Sheet16!B26 |
B20 | B20 | =Sheet17!B48 |
C20 | C20 | =Sheet17!B46 |
D20 | D20 | =Sheet17!B47 |
E20 | E20 | =Sheet17!B45 |
F20 | F20 | =Sheet17!B51 |
G20 | G20 | =Sheet17!B54 |
H20 | H20 | =Sheet17!B53 |
I20 | I20 | =Sheet17!B26 |
B21 | B21 | =Sheet18!B48 |
C21 | C21 | =Sheet18!B46 |
D21 | D21 | =Sheet18!B47 |
E21 | E21 | =Sheet18!B45 |
F21 | F21 | =Sheet18!B51 |
G21 | G21 | =Sheet18!B54 |
H21 | H21 | =Sheet18!B53 |
I21 | I21 | =Sheet18!B26 |
B22 | B22 | =Sheet19!B48 |
C22 | C22 | =Sheet19!B46 |
D22 | D22 | =Sheet19!B47 |
E22 | E22 | =Sheet19!B45 |
F22 | F22 | =Sheet19!B51 |
G22 | G22 | =Sheet19!B54 |
H22 | H22 | =Sheet19!B53 |
I22 | I22 | =Sheet19!B26 |
B23 | B23 | =Sheet20!B48 |
C23 | C23 | =Sheet20!B46 |
D23 | D23 | =Sheet20!B47 |
E23 | E23 | =Sheet20!B45 |
F23 | F23 | =Sheet20!B51 |
G23 | G23 | =Sheet20!B54 |
H23 | H23 | =Sheet20!B53 |
I23 | I23 | =Sheet20!B26 |
B24 | B24 | =Sheet21!B48 |
C24 | C24 | =Sheet21!B46 |
D24 | D24 | =Sheet21!B47 |
E24 | E24 | =Sheet21!B45 |
F24 | F24 | =Sheet21!B51 |
G24 | G24 | =Sheet21!B54 |
H24 | H24 | =Sheet21!B53 |
I24 | I24 | =Sheet21!B26 |
B25 | B25 | =Sheet22!B48 |
C25 | C25 | =Sheet22!B46 |
D25 | D25 | =Sheet22!B46 |
E25 | E25 | =Sheet22!B45 |
F25 | F25 | =Sheet22!B51 |
G25 | G25 | =Sheet22!B54 |
H25 | H25 | =Sheet22!B53 |
I25 | I25 | =Sheet22!B26 |
B26 | B26 | =Sheet23!B49 |
C26 | C26 | =Sheet23!B47 |
D26 | D26 | =Sheet23!B47 |
E26 | E26 | =Sheet23!B46 |
F26 | F26 | =Sheet23!B52 |
G26 | G26 | =Sheet23!B55 |
H26 | H26 | =Sheet23!B54 |
I26 | I26 | =Sheet23!B27 |
A5:A24 | A5 | =WORKDAY(A4,1,$A$1) |
B28:I28 | B28 | =SUM(B4:B27) |