Hello
Can someone please let me know what I'm doing wrong?
I'm still new to the VBA world
I have a bunch of tables that I've created on 1 spreadsheet and hoping I can sort them out at the end of the day
Thanks
Sub EndODay()
'create loop through column F (assuming that's where the Status col. is)
Dim c As Range
For Each c In Range("F10:F80")
If c.Value = "Completed" Then
'will clear the active row's columns A through F
Range(Cells(c.Row, 1), Cells(c.Row, 6)).ClearContents
End If
Next c
'Sort by column F
With ActiveSheet.Sort
.SortFields.Add Key:=Range("F10"), Order:=xlAscending
.SetRange Range("a10:f80")
.Header = xlYes
.Apply
'create loop through column J (assuming that's where the Status col. is)
Dim c As Range
For Each c In Range("J50:J67")
If c.Value = "Yes" Then
'will clear the active row's columns H through J
Range(Cells(c.Row, 8), Cells(c.Row, 10)).ClearContents
End If
Next c
'Sort by column F
With ActiveSheet.Sort
.SortFields.Add Key:=Range("J50"), Order:=xlAscending
.SetRange Range("J50:J67")
.Header = xlYes
.Apply
'create loop through column N (assuming that's where the Status col. is)
Dim c As Range
For Each c In Range("N69:N94")
If c.Value = "Yes" Then
'will clear the active row's columns G through N
Range(Cells(c.Row, 7), Cells(c.Row, 14)).ClearContents
End If
Next c
'Sort by column N
With ActiveSheet.Sort
.SortFields.Add Key:=Range("N69"), Order:=xlAscending
.SetRange Range("N69:N94")
.Header = xlYes
.Apply
'Sort by column M
With ActiveSheet.Sort
.SortFields.Add Key:=Range("M50"), Order:=xlAscending
.SetRange Range("M50:M67")
.Header = xlYes
.Apply
End With
End Sub
Can someone please let me know what I'm doing wrong?
I'm still new to the VBA world
I have a bunch of tables that I've created on 1 spreadsheet and hoping I can sort them out at the end of the day
Thanks
Sub EndODay()
'create loop through column F (assuming that's where the Status col. is)
Dim c As Range
For Each c In Range("F10:F80")
If c.Value = "Completed" Then
'will clear the active row's columns A through F
Range(Cells(c.Row, 1), Cells(c.Row, 6)).ClearContents
End If
Next c
'Sort by column F
With ActiveSheet.Sort
.SortFields.Add Key:=Range("F10"), Order:=xlAscending
.SetRange Range("a10:f80")
.Header = xlYes
.Apply
'create loop through column J (assuming that's where the Status col. is)
Dim c As Range
For Each c In Range("J50:J67")
If c.Value = "Yes" Then
'will clear the active row's columns H through J
Range(Cells(c.Row, 8), Cells(c.Row, 10)).ClearContents
End If
Next c
'Sort by column F
With ActiveSheet.Sort
.SortFields.Add Key:=Range("J50"), Order:=xlAscending
.SetRange Range("J50:J67")
.Header = xlYes
.Apply
'create loop through column N (assuming that's where the Status col. is)
Dim c As Range
For Each c In Range("N69:N94")
If c.Value = "Yes" Then
'will clear the active row's columns G through N
Range(Cells(c.Row, 7), Cells(c.Row, 14)).ClearContents
End If
Next c
'Sort by column N
With ActiveSheet.Sort
.SortFields.Add Key:=Range("N69"), Order:=xlAscending
.SetRange Range("N69:N94")
.Header = xlYes
.Apply
'Sort by column M
With ActiveSheet.Sort
.SortFields.Add Key:=Range("M50"), Order:=xlAscending
.SetRange Range("M50:M67")
.Header = xlYes
.Apply
End With
End Sub
Shift Update.xlsm | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | # of units returned to service | Sign in / Found in: | ||||||||||||||
2 | # of MTO's completed | |||||||||||||||
3 | Oshawa Shift Update | # of BDN's | AM Mechanic | |||||||||||||
4 | # of Sign In's | PM Mechanic | ||||||||||||||
5 | December 18, 2022 | 11:17:12 PM | # of CAD/AVL | OVN Mechanic | ||||||||||||
6 | # of units waiting for reinspect | Unit | W/O # | Activity | Corrected | |||||||||||
7 | # of units waiting for paperwork | |||||||||||||||
8 | Michael Gonsalves | # of absentee | ||||||||||||||
9 | Overnight 20:00-05:00 | # of OT | ||||||||||||||
10 | UNIT | Mechanic | W/O # | Activity | Classification | Status | ||||||||||
11 | 1212 | Anthony Ayoung | sdcs | boot | 5454 | On Hold | ||||||||||
12 | 2122 | 51651 | 6545 | 12451 | 5151 | On Hold | ||||||||||
13 | 2122 | 51651 | 6545 | 5151 | On Hold | |||||||||||
14 | 4152 | Bratish Panjaratnam | 1234567890 | 56446 | Running Repair | TSS | ||||||||||
15 | ||||||||||||||||
16 | ||||||||||||||||
17 | ||||||||||||||||
18 | ||||||||||||||||
19 | ||||||||||||||||
20 | ||||||||||||||||
21 | ||||||||||||||||
22 | ||||||||||||||||
23 | ||||||||||||||||
24 | ||||||||||||||||
25 | ||||||||||||||||
26 | ||||||||||||||||
27 | ||||||||||||||||
28 | MTO STICKERED | RETURNED TO SERVICE | Waiting for Paperwork | |||||||||||||
29 | ||||||||||||||||
30 | ||||||||||||||||
31 | ||||||||||||||||
32 | ||||||||||||||||
33 | ||||||||||||||||
34 | ||||||||||||||||
35 | ||||||||||||||||
36 | ||||||||||||||||
37 | ||||||||||||||||
38 | Waiting for Re-inspect | |||||||||||||||
39 | ||||||||||||||||
40 | ||||||||||||||||
41 | ||||||||||||||||
42 | ||||||||||||||||
43 | ||||||||||||||||
44 | ||||||||||||||||
45 | ||||||||||||||||
46 | ||||||||||||||||
47 | ||||||||||||||||
48 | CAD / AVL / Presto | Attendance | ||||||||||||||
49 | ||||||||||||||||
50 | Unit | Ticket # | Repaired | Reason | Employee | Position | ||||||||||
51 | dvdv | dfvdfv | ||||||||||||||
52 | olol | uikuik | ||||||||||||||
53 | dfvfdv | dfv | Yes | |||||||||||||
54 | ||||||||||||||||
55 | ||||||||||||||||
56 | gyh | ukiu | ||||||||||||||
57 | ||||||||||||||||
58 | ||||||||||||||||
59 | ||||||||||||||||
60 | ||||||||||||||||
61 | ||||||||||||||||
62 | ||||||||||||||||
63 | ||||||||||||||||
64 | ||||||||||||||||
65 | ||||||||||||||||
66 | ||||||||||||||||
67 | ||||||||||||||||
68 | Unit | W/O # | Vendor | Off Property | Away Date | Activity | Email Sent | On Property | ||||||||
69 | ||||||||||||||||
70 | ||||||||||||||||
71 | ||||||||||||||||
72 | ||||||||||||||||
73 | ||||||||||||||||
74 | ||||||||||||||||
75 | ||||||||||||||||
76 | ||||||||||||||||
77 | ||||||||||||||||
78 | ||||||||||||||||
79 | ||||||||||||||||
80 | ||||||||||||||||
81 | SHIFT NOTES | |||||||||||||||
82 | ||||||||||||||||
83 | MORNING | |||||||||||||||
84 | ||||||||||||||||
85 | ||||||||||||||||
86 | ||||||||||||||||
87 | AFTERNOON | |||||||||||||||
88 | ||||||||||||||||
89 | ||||||||||||||||
90 | ||||||||||||||||
91 | OVER NIGHT | |||||||||||||||
92 | ||||||||||||||||
93 | ||||||||||||||||
94 | ||||||||||||||||
Shift Update |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A5 | A5 | =TODAY() |
C5 | C5 | =NOW() |