Exécution macro

refcemad Messages postés 43 Statut Membre -  
Le Pingou Messages postés 12276 Date d'inscription   Statut Contributeur Dernière intervention   -
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

3 réponses

  1.
    cousinhub29 Messages postés 1114 Date d'inscription   Statut Membre Dernière intervention   380
     
    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
    0
  2. refcemad Messages postés 43 Statut Membre 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
    0
  3. Le Pingou Messages postés 12276 Date d'inscription   Statut Contributeur Dernière intervention   1 478
     
    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
    0

Discussions similaires

Transformer un chiffre en lettres dans excel sans macro
vincentphilippos -
tontong -

6 réponses
recup couleur de cellule sans macro
magouero -
JvDo -

7 réponses
convertir des chiffres en lettres avec une unique formule
FOUAD_9000 -
Mike-31 -

34 réponses