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
| Private Sub CommandButton1_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim TableItem As PivotItem
Dim pvt As PivotTable
'Variables pour le pivot de l'onglet PivotQ
Dim EventHour As Integer
Dim EventDay As Integer
'variables pour l'onglet Repartition
Dim rowQ As Integer
Dim columnQ As Integer
'Pour commencer a la deuxieme ligne
rowQ = 1
'la colonne reste toujours la meme
columnQ = 3
Application.Calculation = xlManual
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
If ws.AutoFilterMode Then
ws.AutoFilterMode = False
End If
Next ws
'De lundi a dimanche
For EventDay = 1 To 7
'Durant toute la journee
For EventHour = 0 To 23
'selection de l'onglet PivotQ
Sheets("PivotQ").Visible = xlSheetVisible
Sheets("PivotQ").Activate
' Definition de pvt comme le pivot de l'onglet PivotQ
Set pvt = Sheets("PivotQ").PivotTables(1)
'Selectionner de premier item du pivot
Set TableItem = pvt.GetPivotData("Min spend on Queing", A7, "LC Eventhour", EventHour, "LC Day", EventDay)
'copier cette item
TableItem.Copy
'selection d'onglet Repartition
Sheets("Repartition").Visible = xlSheetVisible
Sheets("Repartition").Activate
rowQ = rowQ + 1
'selection de la premiere cellule de la colonne Queue
Cells(rowQ, columnQ).Select
'Coller l'item
TableItem.Paste
Next EventHour
'passer une ligne
rowQ = rowQ + 1
Next EventDay
End Sub |
Partager