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 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
| Option Explicit
Private Sub cmdInsererMois_Click()
Dim nbMois As Integer
Dim i As Integer
Application.ScreenUpdating = False
nbMois = Month(Date)
'met référence 20XX dans la cellule C1
Range("C1") = "Référence " & Year(Date) - 1
'met Objectif dans la cellule D1
Range("D1").Select
ActiveCell.FormulaR1C1 = "Objectif"
'met evolution vs M-1 dans E1
Range("E1").Select
ActiveCell.FormulaR1C1 = "Evolution vs M-1"
'insert le nombre de colonnes nécéssaires a partir de la colonne E
Columns("E").Resize(, nbMois).Insert
For i = 1 To nbMois
With Cells(1, 4 + i)
.Value = DateSerial(Year(Date), i, 1)
.NumberFormat = "mmmm"
End With
Next
'End Sub
Dim monFichierSource As String
Dim l As Integer
Dim j As Integer
Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim mois As Integer
monFichierSource = "Source"
Set wbk1 = ThisWorkbook
Set wbk2 = Workbooks.Open(Filename:="D:\Documents and Settings\Bureau\" & monFichierSource & ".xlsx")
' Boucle sur les mois pour copier les cellules
'Clients Par Catégorie
For mois = 1 To Month(Date)
For j = 5 To mois
For i = 2 To 72
wbk2.Sheets("stock").Select
If Cells(i, 3) = "Vetements" Then
wbk1.Worksheets(1).Cells(37, j).Value = Round(wbk2.Worksheets("stock").Cells(i, 5).Value * 100, 2) & "%"
wbk1.Worksheets(1).Cells(38, j).Value = Round(wbk2.Worksheets("stock").Cells(i, 7).Value, 0)
ElseIf Cells(i, 3) = "Chaussures" Then
wbk1.Worksheets(1).Cells(9, j).Value = Round(wbk2.Worksheets("stock").Cells(i, 5).Value * 100, 2) & "%"
wbk1.Worksheets(1).Cells(10, j).Value = Round(wbk2.Worksheets("stock").Cells(i, 7).Value, 0)
ElseIf Cells(i, 3) = "Produits" Then
wbk1.Worksheets(1).Cells(23, j).Value = Round(wbk2.Worksheets("stock").Cells(i, 5).Value * 100, 2) & "%"
wbk1.Worksheets(1).Cells(24, j).Value = Round(wbk2.Worksheets("stock").Cells(i, 7).Value, 0)
ElseIf Cells(i, 3) = "Cosmetique" Then
wbk1.Worksheets(1).Cells(30, j).Value = Round(wbk2.Worksheets("stock").Cells(i, 5).Value * 100, 2) & "%"
wbk1.Worksheets(1).Cells(31, j).Value = Round(wbk2.Worksheets("stock").Cells(i, 7).Value, 0)
ElseIf Cells(i, 3) = "Manuf" Then
wbk1.Worksheets(1).Cells(2, j).Value = Round(wbk2.Worksheets("stock").Cells(i, 5).Value * 100, 2) & "%"
wbk1.Worksheets(1).Cells(3, j).Value = Round(wbk2.Worksheets("stock").Cells(i, 7).Value, 0)
ElseIf Cells(i, 3) = "Phyto" Then
wbk1.Worksheets(1).Cells(16, j).Value = Round(wbk2.Worksheets("stock").Cells(i, 5).Value * 100, 2) & "%"
wbk1.Worksheets(1).Cells(17, j).Value = Round(wbk2.Worksheets("stock").Cells(i, 7).Value, 0)
'wbk2.Sheets("focus").Select
'If Cells(i, 3) = "Chaussure" Then
'wbk1.Worksheets(1).Cells(13, j).Value = Round(wbk2.Worksheets("focus").Cells(i, 8).Value * 100, 2) & "%"
'wbk1.Worksheets(1).Cells(14, j).Value = Round(wbk2.Worksheets("focus").Cells(i, 7).Value, 0)
'ElseIf Cells(i, 3) = "Manuf" Then
'wbk1.Worksheets(1).Cells(6, j).Value = Round(wbk2.Worksheets("focus").Cells(i, 8).Value * 100, 2) & "%"
'wbk1.Worksheets(1).Cells(7, j).Value = Round(wbk2.Worksheets("focus").Cells(i, 7).Value, 0)
j = j + 1
End If
Next i
Next j
Next mois
Windows("TEST.xlsm").Activate
End Sub |
Partager