+ Reply to Thread
Results 1 to 5 of 5

Insert Multiple Rows and Copy Formulas Only

  1. #1
    Registered User
    Join Date
    05-31-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Insert Multiple Rows and Copy Formulas Only

    Hello,

    I have a macros listed below that would insert the # of rows based on the # you insert in the input box. This is copying everything on the selected cell including all data/text. Is there a way to copy formulas and data validations only?

    Private Sub CommandButton1_Click()

    Dim J, i As Long
    J = Application.InputBox("Type the Number of Rows to be Inserted", , , , , , , 1)
    If J = False Then Exit Sub
    With Selection.Columns(1)
    For i = .Rows.Count To 1 Step -1
    .Cells(i).EntireRow.Copy
    .Cells(i + 1).Resize(J).Insert
    Next
    End With
    Application.CutCopyMode = False


    End Sub
    Last edited by hallelujah; 06-01-2013 at 07:22 PM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Insert Multiple Rows and Copy Formulas Only

    This copies everything then clears the data/text.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-31-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Insert Multiple Rows and Copy Formulas Only

    Quote Originally Posted by AlphaFrog View Post
    This copies everything then clears the data/text.

    Please Login or Register  to view this content.
    Thanks for replying AlphaFrog. I tried inserting new rows when I click on a row with no data/text but has formulas in it and an error message pops up saying no cells were found. Is there a way to debug?

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Insert Multiple Rows and Copy Formulas Only

    Try this...

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-31-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Insert Multiple Rows and Copy Formulas Only

    Awesome, thanks alot AlphaFrog!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1