Add New Stuff

Rayne
Rayne used Ask the Experts™
on
Hello Dave,

This is reference to this previous question:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27653540.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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012
Commented:
As requested.
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
                Application.CutCopyMode = False
                .SpecialCells(xlCellTypeConstants).Value = vbNullString
            End With
            
        End If
    End If
End Sub

Open in new window


See attached.

Cheers,

Dave
AddMoreRows-r1.xlsm

Author

Commented:
Perfect, that was exactly perfect :)
Thank you Dave

Author

Commented:
Thank you Dave
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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..
Most Valuable Expert 2012
Top Expert 2012

Commented:
That's correct.  Good digging!

Author

Commented:
Thanks Dave, I study your code very well :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial