Exécution macro

refcemad -  
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

  • Votes
  • Date
  • Date décroissante
  1. cousinhub29
    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
    1. refcemad Auteur
      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
      1. Le Pingou
        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
        vincentphilippos

        6 réponses
        recup couleur de cellule sans macro
        magouero

        7 réponses