Avatar of Rayne
Rayne
Flag for United States of America asked on

Add New Stuff

Hello Dave,

This is reference to this previous question:

https://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
Microsoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
Rayne

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
dlmille

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
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!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rayne

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