Sub Crea_Annata()
Dim sh1 As Worksheet: Set sh1 = Worksheets("TurnoAnnuale")
Dim sh2 As Worksheet: Set sh2 = Worksheets("Nomi")
Dim X As Long, Y As Long, W As Long, R As Long, DData As Date
Application.ScreenUpdating = False
Dim T As Single
T = Timer
sh2.Columns("K").ClearContents
R = sh2.Cells(1, 12)
For W = 1 To 3
For X = 1 To 52
For Y = 3 To 9
sh2.Cells(R, 11) = sh2.Cells(X, Y)
R = R + 1
Next Y
Next X
Next W
Y = 3
R = sh2.Cells(6, 12)
sh1.Range("B3:AF31").ClearContents
For X = 2 To 32
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 30, 11)).Copy
sh1.Range("b4").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
R = R + 31
If sh1.Cells(1, 1) Mod 4 = 0 Then
For X = 2 To 30
DData = sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1)
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(DData - 1)), 1)
Next X
'sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
'Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 28, 11)).Copy
sh1.Range("b6").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
R = R + 29
Else
For X = 2 To 29
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 27, 11)).Copy
sh1.Range("b6").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
R = R + 28
End If
For X = 2 To 32
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
'sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1))), 1)
Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 30, 11)).Copy
sh1.Range("b8").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
R = R + 31
For X = 2 To 31
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 29, 11)).Copy
sh1.Range("b10").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
R = R + 30
For X = 2 To 32
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 30, 11)).Copy
sh1.Range("b12").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
R = R + 31
For X = 2 To 31
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 29, 11)).Copy
sh1.Range("b14").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
R = R + 30
For X = 2 To 32
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 30, 11)).Copy
sh1.Range("b16").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
R = R + 31
For X = 2 To 32
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 30, 11)).Copy
sh1.Range("b18").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
R = R + 31
For X = 2 To 31
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 29, 11)).Copy
sh1.Range("b20").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
R = R + 30
For X = 2 To 32
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 30, 11)).Copy
sh1.Range("b22").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
R = R + 31
For X = 2 To 31
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 29, 11)).Copy
sh1.Range("b24").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
R = R + 30
For X = 2 To 32
sh1.Cells(Y, X) = Left(WeekdayName(Weekday(sh1.Cells(2, X) & "/" & sh1.Cells(Y, 1) & "/" & sh1.Cells(1, 1) - 1)), 1)
Next X
Y = Y + 2
sh2.Range(sh2.Cells(R, 11), sh2.Cells(R + 30, 11)).Copy
sh1.Range("b26").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.ScreenUpdating = True
'MsgBox "Eseguito in :" & " " & Timer - T & " " & " minuti/secondi"
Set sh1 = Nothing
Set sh2 = Nothing
riposiea
Pasqua
colorasabato
coloradomenica
griglia
Range("B1").Select
End Sub
|