VBA Code:
Sub ExportEL()
Dim ct As Long, sh As Worksheet, dt As Date
Set sh = ActiveSheet
If Range("AE2") = 1 Then
ct = Day(Application.EoMonth(sh.Range("AD1"), 0))
dt = sh.Range("K1").Value
For I = 1 To ct
dt = dt + 1
Sheets.Add After:=Sheets(Sheets.Count)
With sh
Intersect(.UsedRange, .Columns("T:Aw")).Copy ActiveSheet.Range("A1")
End With
Sheets(Sheets.Count).Name = Format(dt, "DD")
Sheets("CSS LOGS").Columns("T:AD").Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Columns("L:O").Hidden = True
Range("AD1").Value = DateAdd("D", I, CDate(ActiveCell.Value) - 1)
Next
Else: MsgBox "ENTER FIRST DAY OF MONTH", vbOKOnly
End If
End Sub
Hello everyone. Using the above code I able to Copy the Active sheet From Column(T:AW). when T:AW is copied to new sheets in A1 the date value is in K1 for each sheet. I want the value of each new sheet to and 1 date to previous sheet. I would like to also change the code to instead of coping to new sheet to copy all new sheets to another workbook and name the workbook the name of the Month. Below is a copy of the area that is being copied. any help is greatly apperactied.
CSS New Exception Log Layout.xlsm | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
T | U | V | W | X | Y | Z | AA | AB | AC | AD | |||
1 | 12/1/2020 | ||||||||||||
2 | EE# | LAST NAME | FIRST NAME | PT/FT STATUS | SCHEDULE | ACTUAL | OT | DT | MDT | SUP INITIAL | REMARKS | ||
3 | 0 | FT | |||||||||||
4 | 0 | FT | |||||||||||
5 | 132832 | ANDERSON | KAT | FT | |||||||||
6 | 20506 | ANDERSON | TOMAS | FT | |||||||||
7 | 130219 | BAKERFIELD | MORGAN | FT | |||||||||
8 | 123691 | CRIG | JOHN | FT | |||||||||
9 | 48706 | DAVIS | BRIAN | FT | |||||||||
10 | 69803 | DES | RANDIE | FT | |||||||||
11 | 125327 | GEH | TYLOR | FT | |||||||||
12 | 111518 | HAN | ABO | FT | |||||||||
13 | 39702 | JAMES | LISA | FT | |||||||||
14 | 125812 | JOHNS | JENN | FT | |||||||||
15 | 39615 | MCCLUR | DAVID | FT | |||||||||
16 | 53996 | MILLER | ERIK | FT | |||||||||
17 | 98251 | RENAY | CLINT | FT | |||||||||
18 | 38792 | RUSSELL | BOB | FT | |||||||||
19 | 101941 | SMITH | JASON | FT | |||||||||
20 | 18235 | SMITH | WILL | FT | |||||||||
21 | 9838 | WHITE | BILLY | FT | |||||||||
22 | 121518 | WHITE | MELISSA | FT | |||||||||
23 | 114674 | WILLIAMS | JESSICA | FT | |||||||||
24 | 27415 | WILLS | MIKE | FT | |||||||||
25 | 121967 | WRIGHT | DANIEL | FT | |||||||||
CSS LOGS |
Cell Formulas | ||
---|---|---|
Range | Formula | |
T3:T25 | T3 | =IFERROR(INDEX('CSS LIST'!$B$2:$B$102,MATCH(AGGREGATE(15,6,'CSS LIST'!$E$2:$E$102,ROW(T1)),'CSS LIST'!$E$2:$E$102,0)),"") |
U3:U25 | U3 | =IFERROR(INDEX('CSS LIST'!$D$2:$D$102,MATCH(T3,'CSS LIST'!$B$2:$B$102,0)),"") |
V3:V25 | V3 | =IFERROR(INDEX('CSS LIST'!$C$2:$C$102,MATCH(T3,'CSS LIST'!$B$2:$B$102,0)),"") |
X3 | X3 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T3,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T3,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X4 | X4 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T4,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T4,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X5 | X5 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T5,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T5,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X6 | X6 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T6,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T6,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X7 | X7 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T7,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T7,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X8 | X8 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T8,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T8,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X9 | X9 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T9,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T9,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X10 | X10 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T10,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T10,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X11 | X11 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T11,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T11,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X12 | X12 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T12,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T12,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X13 | X13 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T13,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T13,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X14 | X14 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T14,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T14,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X15 | X15 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T15,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T15,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X16 | X16 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T16,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T16,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X17 | X17 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T17,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T17,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X18 | X18 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T18,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T18,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X19 | X19 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T19,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T19,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X20 | X20 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T20,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T20,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X21 | X21 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T21,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T21,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X22 | X22 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T22,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T22,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X23 | X23 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T23,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T23,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X24 | X24 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T24,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T24,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
X25 | X25 | =IFERROR(IF(AE$1=TRUE,INDEX('CSS LOGS'!$D$3:$J$102,MATCH(T25,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$D$2:$J$2,0)),IF($AF$1=TRUE,INDEX('CSS LOGS'!$L$3:$R$102,MATCH(T25,'CSS LOGS'!$A$3:$A$102,0),MATCH(AH1,'CSS LOGS'!$L$2:$R$2,0)),"")),"") |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'CSS LOGS'!Print_Area | ='CSS LOGS'!$T$1:$AD$102 | T3 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
X3:X102 | Cell Value | contains "OFF" | text | NO |