Exécution macro

refcemad Messages postés 39 Date d'inscription Statut Membre Dernière intervention -
Le Pingou Messages postés 12249 Date d'inscription Statut Contributeur Dernière intervention - 12 janv. 2011 à 15:35

Bonjour,

J'ai enregistré une macro qui me calcule un certain nombre de choses et me les présente sous forme de tableau. Mais je butte sur un problème. En effet, je souhaite qu'à chaque exécution un nouveau tableau soit créé, mettons, deux lignes en dessous du précédent. (mais ne jamais modifier les précédents bien entendu).

Mes connaissances en VBA étant particulièrement minces, je ne vois pas comment faire. Je me suis penchée sur les références relatives et absolues mais je ne trouve pas ce que je cherche.

Merci pour vos conseils.
Cordialement

Afficher la suite

A voir également:

Exécution macro
Telecharger macro convertir chiffre en lettre excel - Télécharger - Tableur
Jitbit macro recorder - Télécharger - Confidentialité
Télécharger macro convertir chiffre en lettre excel - Télécharger - Tableur
Macro word - Guide
Macro express - Télécharger - Divers Utilitaires

3 réponses

Réponse 1 / 3

cousinhub29 Messages postés 1074 Date d'inscription Statut Membre Dernière intervention 361

Bonjour,

Afin de répondre au mieux à ta demande, le plus simple serait que tu mettes le code que tu utilises, pour que nous puissions voir comment le modifier....

PS, pour mettre un code dans le forum, pense à utiliser la balise "code", le raccourci le plus à droite de la fenêtre de réponse (à côté du S)

Bonne journée

Réponse 2 / 3

refcemad Messages postés 39 Date d'inscription Statut Membre Dernière intervention 1

Merci de votre intérêt cousinhub29.

Voici mon code, attention les yeux !!!
Je sais, j'aurais dû le nettoyer, mais dédutante que je suis, j'ai grand peur de faire des bêtises. Veuillez m'excuser.

Sub enregistrement_mensuel()
'
' enregistrement_mensuel Macro
' Macro enregistrée le 11/01/2011 par TECHNIQUE
'
' Touche de raccourci du clavier: Ctrl+Maj+A
'
Range("C10:I10").Select
Range("C10:H10").Select
Range("H10").Activate
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Range("C10:H10").Select
ActiveCell.FormulaR1C1 = "Nb de caisses / préformes livrées au :"
Range("I10").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("I10").Select
ActiveCell.FormulaR1C1 = "1/11/2011"
Range("C5:I5").Select
Selection.Copy
Range("C11").Select
ActiveSheet.Paste
Range("C12").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=Matière!R[22]C[14]"
Range("D12").Select
ActiveCell.FormulaR1C1 = "=Matière!R[22]C[14]"
Range("E12").Select
ActiveCell.FormulaR1C1 = "=Matière!R[22]C[14]"
Range("F12").Select
ActiveCell.FormulaR1C1 = "=Matière!R[22]C[14]"
Range("G12").Select
ActiveCell.FormulaR1C1 = "=Matière!R[22]C[14]"
Range("H12").Select
ActiveCell.FormulaR1C1 = "=Matière!R[22]C[14]"
Range("I12").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
Range("C12:I12").Select
Range("I12").Activate
Selection.Copy
Range("C13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("C12:I12").Select
Selection.Copy
Range("C13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C13:I13").Select
Application.CutCopyMode = False
Range("C13:I13").Cut Destination:=Range("C12:I12")
Range("C10:H10").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("C11:I12").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("C12:I12").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("I12").Select
Selection.Font.Bold = True
Range("C10:I10").Select
Range("I10").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("C10:H10").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("I10").Select
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("F15").Select
Columns("I:I").EntireColumn.AutoFit
Range("I11:I12").Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Range("C13").Select
ActiveCell.FormulaR1C1 = "=Matière!R[56]C[14]"
Range("C14").Select
Sheets("Matière").Select
Range("Q69:W69").Select
Selection.Copy
Sheets("Feuil1").Select
Range("C13").Select
Range("D13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=Matière!R[56]C[14]"
Range("E13").Select
ActiveCell.FormulaR1C1 = "=Matière!R[56]C[14]"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=Matière!R[56]C[14]"
Range("G13").Select
ActiveCell.FormulaR1C1 = "=Matière!R[56]C[14]"
Range("H13").Select
ActiveCell.FormulaR1C1 = "=Matière!R[56]C[14]"
Range("I13").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
Range("C13:H13").Select
Range("H13").Activate
Selection.Copy
Range("C14").Select
Range("C13:H13").Select
Application.CutCopyMode = False
Selection.Copy
Range("C13:I13").Select
Range("I13").Activate
Application.CutCopyMode = False
Selection.Copy
Range("C14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("C14:I14").Cut Destination:=Range("C13:I13")
Range("C13:I13").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("C12:I13").Select
Range("I12").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("C11:I13").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("I13").Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Selection.Font.Bold = True
Range("B12").Select
ActiveCell.FormulaR1C1 = "Nb caisses"
Range("B13").Select
ActiveCell.FormulaR1C1 = "Nb préformes"
Range("I10").Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
Range("C10:I10").Select
Range("I10").Activate
Selection.Interior.ColorIndex = 40
Range("C10:H10").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("B12:B13").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B12:I12").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("C11:I11").Select
Range("I11").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("I10").Select
Selection.NumberFormat = "[$-40C]d-mmm-yy;@"
Range("H16").Select
Columns("I:I").EntireColumn.AutoFit
Range("C16").Select
End Sub

Réponse 3 / 3

Le Pingou Messages postés 12249 Date d'inscription Statut Contributeur Dernière intervention 1 458

Bonjour,
Juste au passage, il n'y a visiblement aucun calcul dans votre macro.I
Il serait plus avantageux de mettre directement votre classeur sur https://www.cjoint.com/ et poster le lien.
Une petite explication directement sur une des feuille du classeur.
Salutations.
Le Pingou

Discussions similaires

Transformer un chiffre en lettres dans excel sans macro

recup couleur de cellule sans macro

EXCEL transformer chiffres en lettre -facture

convertir des chiffres en lettres avec une unique formule

Problème de périphériques

Votre réponse

Discussions similaires