Agnarr
New Member
- Joined
- Jan 15, 2023
- Messages
- 29
- Office Version
- 365
- Platform
- Windows
Hello everyone.
I would like to create a button which when pressed should Create 12 New Sheets, each named after a month (JANUARY, FEBRUARY, etc) and each sheet should have the same appearance modeled after this one:
Note that there is a vba code that should apply to all new sheets:
Any help or recommendations of how i could do something like that will be most appreciated.
Thank you all in advance.
I would like to create a button which when pressed should Create 12 New Sheets, each named after a month (JANUARY, FEBRUARY, etc) and each sheet should have the same appearance modeled after this one:
Πρόγραμμα Aqua 2024.xlsm | |||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | |||
3 | ΔΕΚΕΜΒΡΙΟΣ | Δευ | Τρι | Τετ | Πεμ | Παρ | Σαβ | Κυρ | Δευ | Τρι | Τετ | Πεμ | Παρ | Σαβ | Κυρ | Over Time | |||||||||||||||||||
4 | W1 | W2 | Employee Name | 27-Nov | 28-Nov | 29-Nov | 30-Nov | 01-Dec | 02-Dec | 03-Dec | 04-Dec | 05-Dec | 06-Dec | 07-Dec | 08-Dec | 09-Dec | 10-Dec | ||||||||||||||||||
5 | 48.00 | ΜΠΑΡΜΠΑΣ | 09:00 | 21:00 | 09:00 | 18:00 | Sol | Tag | Day | Off | 09:00 | 19:00 | 09:00 | 18:00 | 13:00 | 21:00 | 09:00 | 21:00 | 09:00 | 18:00 | Sol | Tag | 5 | 8 | |||||||||||
6 | 45.00 | ΚΑΡΑΠΕΤΣΑΣ | 09:00 | 17:00 | 10:00 | 18:00 | Sol | Tag | 09:00 | 18:00 | 13:00 | 21:00 | Day | Off | 09:00 | 21:00 | 13:00 | 21:00 | 10:00 | 18:00 | Sol | Tag | 0 | 5 | |||||||||||
7 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
8 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
9 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
10 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
11 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
12 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
13 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
14 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
15 | Δευ | Τρι | Τετ | Πεμ | Παρ | Σαβ | Κυρ | Δευ | Τρι | Τετ | Πεμ | Παρ | Σαβ | Κυρ | Over Time | ||||||||||||||||||||
16 | W3 | W4 | Employee Name | 11-Dec | 12-Dec | 13-Dec | 14-Dec | 15-Dec | 16-Dec | 17-Dec | 18-Dec | 19-Dec | 20-Dec | 21-Dec | 22-Dec | 23-Dec | 24-Dec | ||||||||||||||||||
17 | 48.00 | 48.00 | ΜΠΑΡΜΠΑΣ | 09:00 | 18:00 | 11:00 | 21:00 | Day | Off | 13:00 | 21:00 | 09:00 | 21:00 | 09:00 | 18:00 | Sol | Tag | Day | Off | 09:00 | 19:00 | 09:00 | 18:00 | 13:00 | 21:00 | 09:00 | 21:00 | 09:00 | 18:00 | Sol | Tag | 8 | 8 | ||
18 | 40.00 | 40.00 | ΚΑΡΑΠΕΤΣΑΣ | Day | Off | 09:00 | 17:00 | 10:00 | 18:00 | 09:00 | 17:00 | 09:00 | 17:00 | 10:00 | 18:00 | Sol | Tag | 10:00 | 18:00 | 13:00 | 21:00 | Day | Off | 09:00 | 17:00 | 13:00 | 21:00 | 10:00 | 18:00 | Sol | Tag | 0 | 0 | ||
19 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
20 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
21 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
22 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
23 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
24 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
25 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
26 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
27 | Δευ | Τρι | Τετ | Πεμ | Παρ | Σαβ | Κυρ | Δευ | Τρι | Τετ | Πεμ | Παρ | Σαβ | Κυρ | Over Time | ||||||||||||||||||||
28 | W5 | W6 | Employee Name | 25-Dec | 26-Dec | 27-Dec | 28-Dec | 29-Dec | 30-Dec | 31-Dec | 01-Jan | 02-Jan | 03-Jan | 04-Jan | 05-Jan | 06-Jan | 07-Jan | ||||||||||||||||||
29 | 39.00 | ΜΠΑΡΜΠΑΣ | National | DayOff | 11:00 | 21:00 | Day | Off | 13:00 | 21:00 | 09:00 | 21:00 | 09:00 | 18:00 | Sol | Tag | Sol | Tag | 7 | 0 | |||||||||||||||
30 | 40.00 | ΚΑΡΑΠΕΤΣΑΣ | National | DayOff | 09:00 | 17:00 | 10:00 | 18:00 | 09:00 | 17:00 | 09:00 | 17:00 | 10:00 | 18:00 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||
31 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
32 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
33 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
34 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
35 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
36 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
37 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
38 | Sol | Tag | Sol | Tag | 0 | 0 | |||||||||||||||||||||||||||||
ΔΕΚΕΜΒΡΙΟΣ |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F4,H4,J4,L4,N4,P4,R4,T4,V4,X4,Z4,AB4,AD4,F28,H28,J28,L28,N28,P28,R28,T28,V28,X28,Z28,AB28,AD28,F16,H16,J16,L16,N16,P16,R16,T16,V16,X16,Z16,AB16,AD16 | F4 | =D4+1 |
A5:A14,A29:A38,A17:A26 | A5 | =IF(D5="","",SUM(AH5:AN5)) |
B5:B14,B29:B38,B17:B26 | B5 | =IF(S5="","",SUM(AO5:AU5)) |
AF5:AG14,AF29:AG38,AF17:AG26 | AF5 | =IF(BM5=0,0,BM5) |
D16,D28 | D16 | =AD4+1 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D5:AE38 | Dates Occurring | today | text | NO |
D5:AE38 | Expression | =OR(D5="Day",D5="Off") | text | NO |
D5:AE38 | Expression | =OR(D5=0.416666666666667,D5=0.75,D5=0.458333333333333,D5=0.791666666666667) | text | NO |
D5:AE38 | Expression | =OR(D5="VACAY",D5="Sick",D5="Leave",D5="National",D5="DayOff",D5="Vacation",D5="Time",D5="sol",D5="tag") | text | NO |
D5:AE38 | Expression | =OR(D5=0.541666666666667,D5=0.875) | text | NO |
D5:AE38 | Expression | =OR(D5=0.375,D5=0.708333333333333) | text | NO |
D5:O14,R5:AC14,D17:O26,R17:AC26,D29:O38,R29:AC38 | Expression | =OR(D5=0.416666666666667,D5=0.75,D5=0.458333333333333,D5=0.791666666666667) | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
D5:AE14 | List | =Holidays!$F$1:$F$12 |
D29:AE38 | List | =Holidays!$F$1:$F$12 |
D17:AE26 | List | =Holidays!$F$1:$F$12 |
Note that there is a vba code that should apply to all new sheets:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim inputR As Range, codeR As Range, cell As Range
Dim f, fnd As Range
Set inputR = Range("c4:ad70")
If Intersect(Target, inputR) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
' Check if the changed cell is being deleted or changed to nothing
If WorksheetFunction.CountBlank(Target) > 0 Then Exit Sub
Set fnd = Sheets("Holidays").Range("F:F").Find(Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not fnd Is Nothing Then
Target.Offset(, 1) = fnd.Offset(, 2)
End If
Application.ScreenUpdating = True
Set codeR = Worksheets("Holidays").Range("F:F")
If Target.CountLarge > 1 Then Exit Sub
With Application
.EnableEvents = False
For Each cell In Target
' Check if the cell value is being deleted or changed to nothing
If cell.Value <> "" Then
Set f = codeR.Find(cell.Value, , , xlWhole)
If Not f Is Nothing Then cell.Value = f.Offset(, 1).Value
End If
Next
.EnableEvents = True
End With
End Sub
Any help or recommendations of how i could do something like that will be most appreciated.
Thank you all in advance.