Programmation - developpement

[groota]

Bonjour, j’aurais besoin d’un peu d’aide par rapport à la programmation d’un macro sur excel. 

À partir d'un tableau de données, je voudrais parvenir à créer un tableau croisé dynamique de manière automatique, et cela grâce à un macro.
Le problème c'est que la taille de mon tableau varie (pas en nombre de colonnes mais en nombre de lignes).

Pour l'instant mon code est : (si quelqu’un a le courage de le lire)

Code:

Sub TOUT()
'
' TOUT Macro
' Macro enregistrée le 10/06/2009 par Administrateur
'
' Touche de raccourci du clavier: Ctrl+Maj+W
'
Range("E14").Select
[...]
ActiveWindow.ScrollColumn = 3

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R120C11").CreatePivotTable TableDestination:="", TableName:= _
"Tableau croisé dynamique1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddFields RowFields:= _
Array("Année", "Mois"), ColumnFields:="Site du correspondant", PageFields:= _
Array("Nature", "État")
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Dossiers"). _
Orientation = xlDataField
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotSelect "", _
xlDataAndLabel, True
ActiveSheet.PivotTables("Tableau croisé dynamique1").Format xlTable2
Range("A12").Select
Selection.Delete
Range("A12:F12").Select
Range("F12").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
Range("A2,F6:F12").Select
Range("F6").Activate
With Selection.Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A1").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A2").Select
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1,B6:B12,C6:F13").Select
Range("C13").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D21").Select
Range("C5:F5").Select
Range("F5").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Interior
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A13:F13").Select
Range("F13").Activate
With Selection.Interior
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A6").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
With Selection.Interior
.ColorIndex = 47
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("D20").Select
Columns("A:A").ColumnWidth = 13
Columns("A:A").Select
Selection.ColumnWidth = 13.12
Range("D22").Select
Range("B11").Select
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Mois"). _
PivotItems("Jan").Position = 1
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Mois"). _
PivotItems("Fev").Position = 2
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Mois"). _
PivotItems("Mar").Position = 3
Range("A6").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("A12:B12").Select
Range("B12").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
Range("D21").Select
Range("A12").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Range("B12").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone







Range("A11:B11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("A6").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("G3").Select

End Sub

J’ai deux problèmes face à ce code :

1. Cela ne marche que pour des tableaux de 120 lignes
(J'ai trouvé sur un forum l'astuce : Range("A1:K" & ActiveCell.Row).Select pour selectionner la cellule active mais je ne sais pas comment faire pour la suite car "Sheet1!R1C1:ActiveCell.Row") cela ne marche pas.)

2. Lorsque je filtre une caractéristique de mon TCD, deux erreurs de mise en page apparaissent :
- il y a une bordure en haut des cases A12 et B12
- La couleur de l'écriture de la case A6 n'est plus blanche, mais violet comme le fond de la case ...

Merci infiniment pour ceux qui me liront et me répondront. ^^

[Thierry]

Hello,

Ça sent l'enregistreur de macro .

Pour le problème de ligne, dans

Code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R120C11").CreatePivotTable TableDestination:="", TableName:= _

remplacer Sheet1!R1C1:R120C11
par Sheet1!A:K


L'enregistreur de macro est très utile mais il faut repasser derrière lui pour rendre le code plus général, comme dans mon exemple.

[groota]

Merci énormément Thierry pour ton aide !  En effet, il me prend toutes mes lignes maintenant.
Oui tu a raison j'ai utilisé l'enregistreur de macro ... mais je n'ai pas trouvé de site donnant les formules à utiliser pour la mise en page ...
Est ce que tu vois d'où peut venir mon 2eme problème ? Car le tableau apparait tout d'abord comme je le souhaite, puis il suffit que je filtre quleque chose, pour qu'une couleur change et qu'un trait apparaisse alors qu'il ne devrait pas ...

Merci ^^

[groota]

Code:

Sub Code()
'
' Code Macro
' Macro enregistrée le 12/06/2009 par Administrateur
' Touche de raccourci du clavier: Ctrl+Maj+C
'
'Préparation des données
Range("E14").Select
[...]
ActiveWindow.ScrollColumn = 3
'Fin de la préparation des données

'Préparation du TCD
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!A:K").CreatePivotTable TableDestination:="", TableName:= _
"Tableau croisé dynamique1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddFields RowFields:= _
Array("Mois"), ColumnFields:="Site du correspondant", PageFields:= _
Array("Nature", "État")
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Dossiers"). _
Orientation = xlDataField
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotSelect "", _
xlDataAndLabel, True
'Fin de la préparation du TCD

'Construction de la mise en page
'Masquer la ligne vide :
    Range("A12").Select
    Selection.Delete
   
Range("A1,A2,A4,A5,B4").Select
Selection.Interior.ColorIndex = 2
Selection.Font.FontStyle = "Gras"
Selection.Font.Size = 9
   
Range("B5,C5,D5,E5,A12,B12,C12,D12,E12").Select
Selection.Interior.ColorIndex = 24
Selection.Font.FontStyle = "Gras"
Selection.HorizontalAlignment = xlCenter

Range("E6:E11").Select
Selection.Font.FontStyle = "Gras"
Selection.HorizontalAlignment = xlCenter

Range("A6:D11").Select
Selection.HorizontalAlignment = xlCenter

Range("A6:A11,E6:E11,B6:D11").Select
Selection.Interior.ColorIndex = 2

End Sub

Merci beaucoup pour ton aide Thierry 

[groota]

Je voudrais maintenant générer un graphique à partir de ce tableau. Quelqu'un aurait-il des conseils et des formules à me donner car je ne trouve rien sur internet ?
Merci 

[Thierry]

pour la mise en forme ii faut comprendre les étapes de ton code.

En gros :
1 - tu crées un TCD par le code
2 - tu ajustes la mise en page par le code (en faisant ça tu annules la mise en forme auto du TCD)
3 - tu actualises ton TCD par l'interface graphique ( et là ton TCD recrée sa mise en forme auto)

A mon avis tu dois creuser ce point :
comment rendre la mise en forme plus objective, c'est-à-dire éviter le A6 = violet, mais plutôt : si la cellule contient "un critère à déterminer" alors couleur = violet.

Il y a la mise en forme conditionnelle pour ça dans les options de l'interface graphique.
Peut être cette option résoudra  ton problème (quand tu update ton TCD, la mise en forme s'actualise)


Pour le graphique, normalement en faisant un clic droit sur ton TCD tu dois trouver l'option.

[groota]

Merci !!!   

Encore un dernier truc, quand je veux créer un bouton racourci de mon macro, il apparait nickel et il marche. Le problème, c'est que quand je ferme mon fichier excel, puis que je le rouvre, le bouton a disparu ... Y a t-il une explication ?

[Thierry]

c'est étrange en effet.

Il n'y a apparemment aucune raison pour que le bouton n'apparaisse plus. J'avoue que je ne vois pas trop la solution.

[groota]

Ok merci quand même 
et pour lui imposer d'aller sur une feuille (par exemple Sheet1), c'est quelle commande à utiliser ?

[groota]

A mon avis tu dois creuser ce point :
comment rendre la mise en forme plus objective, c'est-à-dire éviter le A6 = violet, mais plutôt : si la cellule contient "un critère à déterminer" alors couleur = violet.

Comment je fais ça ? 

[Thierry]

si tu veux faire par le code, peut-être utiliser l'évènement :

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

de la feuille.

ensuite fait une recherche dans l'aide VBA sur

Code:

FormatConditions.Méthode Add

Sinon, en utilisant la mise en forme classique, il y a une option "mise en forme conditionnelle" de mémoire. Cherche dans les menus.