1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
| Option Explicit
Const shtSem1Name As String = "Janvier-Juillet"
Const shtSem2Name As String = "Aout-Décembre"
Dim vrange As Range, vrange2 As Range
Function impression()
Range("AB8:BH8").ClearContents
Dim shtSem(1 To 2) As Worksheet
Dim numSht As Byte, col As Long, col2 As Long, col3 As Long, col4 As Long
With ThisWorkbook
Set shtSem(1) = .Worksheets(shtSem1Name)
Set shtSem(2) = .Worksheets(shtSem2Name)
End With
With shtSem(1 + Abs((Month(Date) > 7)))
col = .Application.Match(Date * 1, .Rows(4), 0)
col2 = col + 27
.Range("A5:AA29").Copy Destination:=Sheets("Impression").Range("A9")
Range("A9:AA40").Font.Size = 6
Dim L As Integer
For L = 1 To 34
If Cells(L, 16).Value = 1 Then
Cells(L, 16).ClearContents
Cells(L, 13).Value = 5
Cells(L, 14).Value = 2
End If
Next
If .Cells(4, col2).Value = "" Then
col2 = .Range("IV4").End(xlToLeft).Column
col3 = 29
Dim intrmd As Integer
intrmd = 256 - col2
col4 = col3 + intrmd
If (1 + Abs((Month(Date) > 7))) = 1 Then
With shtSem(2)
Set vrange2 = .Range(.Cells(4, col3), .Cells(29, col4))
End With
Else
With shtSem(1)
Set vrange2 = .Range(.Cells(4, col3), .Cells(29, col4))
End With
End If
End If
Set vrange = .Range(.Cells(4, col), .Cells(29, col2))
End With
vrange.Copy
Range("AB8").PasteSpecial Paste:=xlPasteValues
Range("AB8").PasteSpecial Paste:=xlPasteFormats
Selection.Font.Size = 6
Dim fin As Long
fin = Range("IV8").End(xlToLeft).Column + 1
vrange2.Copy
Cells(8, fin).PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone
Selection.Font.Size = 6
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True, Collate:=True
End Function |
Partager