Results 1 to 5 of 5

Copying Selected Row & Formulas and inserting new row below with formulas only

Threaded View

  1. #1
    Registered User
    Join Date
    08-14-2017
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    3

    Copying Selected Row & Formulas and inserting new row below with formulas only

    Hello All,

    My goal that I am trying to achieve is to have a user be able to select a row that contains data within a workseet- hit a macro, and a row will be inserted below the selection. Within this insert I wish the formulas that are contained within the selected row are copied into the new row, but no values. So users are able to manually enter values throughout the protected worksheet, but those values should not be copied line to line.

    So I attempted this with the following VBA- but the problem I am having is, if I hide any rows throughout the worksheet, it seems to completely bug out / delete rows, and does not work properly- and I am not sure why. If I modify the filter through the drop down, it also seems to make the macro act funny. It only seems to work 'somewhat' correctly if I never change the filters/or hide any rows (out of 1000)

    Any help would be appreciated!
    Mike

    Sub CopyAndInsertRow()
            ActiveSheet.Unprotect
            Dim rw As Long
        With Selection
            rw = .Row
            .EntireRow.Copy
            .Insert Shift:=xlDown
            On Error Resume Next
            Cells(rw + 1, 1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
            On Error GoTo 0
        Application.CutCopyMode = False
        ActiveSheet.Protect , AllowFiltering:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
        End With
    End Sub
    Last edited by MikeTR; 08-16-2017 at 10:51 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Inserting a row at the top, copying formulas from below
    By Ebarron in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-19-2014, 07:07 PM
  2. Replies: 3
    Last Post: 04-11-2013, 01:29 PM
  3. Inserting new Rows & copying formulas in protected sheet
    By amlal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2012, 11:19 AM
  4. Copying Formulas When Automatically Inserting a Row
    By tsturtz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-17-2011, 04:25 PM
  5. Multiple formulas in selected cell without damaging previous formulas.
    By excel5111987 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-05-2011, 06:15 AM
  6. Macro for copying formulas + inserting rows
    By Marco-Kun in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-25-2009, 09:41 PM
  7. Replies: 2
    Last Post: 08-18-2006, 11:41 AM

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