asked on

Add New Stuff

Hello Dave,

This is reference to this previous question:

https://www.experts-exchange.com/questions/27653540/Excel-Button-to-copy-rows-and-format.html

I have a excel file - When I click on button, it prompts me for the number of new rows that I want to add, and then adds the new empty rows. Copies the formatting and formulas identical to the previous rows.
..like if I enter 7 …the macro created 7 new rows with formatting and formulas..below the last row data

Thank you
Rayne
AddMoreRows.xlsm

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rayne

ASKER

Perfect, that was exactly perfect :)
Thank you Dave

Rayne

ASKER

Thank you Dave

Rayne

ASKER

Dave,

Your code works awesome but with one thing – it’s copying the color of the last row (which happens to be the first formula row) – so is there a way to copy everything but Not the fill color of the copied cell row?

Thank you
Rayne

dlmille

Do we need to copy formats at all? If not, we can just pastespecial values

Sub Button2_Click()
Dim wkb As Workbook
Dim wks As Worksheet
Dim lastRow As Long
Dim xRows As Variant

    Set wkb = ThisWorkbook
    Set wks = wkb.ActiveSheet
    
    xRows = InputBox("Enter # Rows to Insert", "Row Insert Routine", 0)
    
    If IsNumeric(xRows) Then
        If xRows > 0 Then
            'add rows to table
            
            lastRow = wks.Range("A" & wks.Rows.Count).End(xlUp).Row
            wks.Rows(lastRow).Copy
            With wks.Range(wks.Rows(lastRow + 1), wks.Rows(lastRow + xRows))
                .PasteSpecial xlPasteValues
                Application.CutCopyMode = False
                .SpecialCells(xlCellTypeConstants).Value = vbNullString
            End With
            
        End If
    End If
End Sub

Open in new window

Dave

Rayne

ASKER

Hello Dave,
Because I have look up formulas that need to be copied down and also if number is in dollar or percentage, that’s needs to be copied as well….
PasteSpecial xlPasteFormulasAndNumberFormats
Worked nicely for me, it copied everything exactly -formulas and number formats but didn’t copied the color fill of the first row..

dlmille

That's correct. Good digging!

Rayne

ASKER

Thanks Dave, I study your code very well :)