No announcement yet.

Macro to add rows in separate sheets/tables simultaneously

  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro to add rows in separate sheets/tables simultaneously

    The attached Worsheet has different sheets that communicate with each other.
    "Lead Data" is the main worksheet where all data is entered.
    I already have a Macro which adds a new row above the last row of the table (above row "Total") and it works perfectly.

    In Sheet "Forecasted Sales", the data from Lead data is taken and reorganised.
    I would like the Macro to also add a new Row in the table in "Forecasted Sales" using the same conditions.

    This is the Macro that I'm using now

    Sub Button7_Click()
    Dim Lr As Integer, Fr As Integer
    Fr = Columns("A").Find(What:="Number", After:=Range("A5")).Row 'Searching row of "Number" header
    Lr = Range("A" & Fr).End(xlDown).Row 'Searching last row in Risk table
        Rows(Lr + 1).Insert Shift:=xlDown 'Inserting new row
        Cells(Lr + 1, "A") = Cells(Lr, "A") + 1 'Adding a sequential number
        Rows(Lr).Copy 'Copying format of last row
        Rows(Lr + 1).PasteSpecial Paste:=xlPasteFormats 'Pasting format to new row
        Application.CutCopyMode = False 'Deactivating copy mode
        Cells(Lr + 1, "B").Select
    End Sub

    Attached Files