Results 1 to 4 of 4

Thread: Copy automatically formula and formatting to next row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy automatically formula and formatting to next row

    Hello
    Im new with VBA but can understans. I need help to copy the entire row (formula + formatting) on the empty row downward while hit enter or typing on it.
    Thank you

  2. #2
    Board Regular
    Join Date
    Apr 2015
    Posts
    504
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy automatically formula and formatting to next row

    This code pastes formulas and formatting from column A into column B on doubleClick of cell in the respective range of column B.

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
       Application.ScreenUpdating = False
       lastrow = Cells(Rows.Count, 1).End(xlUp).Row
       Set Target = Range("B1:B" & lastrow)
       
       'The following for loop updates formulas in column A to absolute references. _
       Delete if not necessary.
           For Each c In Range("A1:A" & lastrow)
               c.Value = Application.ConvertFormula(c.Formula, _
                 xlA1, , xlAbsolute)
           Next c
       
       Range("A1:A" & lastrow).Copy
       Range("B1:B" & lastrow).PasteSpecial Paste:=xlPasteFormats
       Range("B1:B" & lastrow).PasteSpecial Paste:=xlPasteFormulas
       Application.CutCopyMode = False
       Application.ScreenUpdating = True
    End Sub

  3. #3
    MrExcel MVP Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,535
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Copy automatically formula and formatting to next row

    Welcome to the Board!

    If you format your existing data range as a table, then any data input in the row immediately beneath it will automatically extend formulas/formatting. No need for VBA...

    HTH,

  4. #4
    New Member
    Join Date
    Sep 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy automatically formula and formatting to next row

    Thank you for your answers.
    I have try to use table but it doesn't work because the formula starts on the 2nd row.
    my data is like this example:
    No-----FROM------TO-------DIFF
    1-------0------------4----------4
    2-------4------------10.3-----5.7
    3-------10.3--------13.3-----3
    ......and so on
    and the end is variable according to each sheet. Maybe from 60 to 2000rows.
    I have just to enter data on "TO" column .
    The formula are:
    [No]: always starts from 1 and I would like that it become 2 when I enter data on cell C3.
    [FROM]: always starts from 0. Also I d like that cell B3 = C2 if I enter data on cell C3.
    It is the same for the [DIFF]: Difference
    So how to copy the formula and the formatting on the next rows.
    Thank you.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •