Results 1 to 4 of 4

Only Copy Formula from the Row Above

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Only Copy Formula from the Row Above

    I am using the following code to insert and delete rows on a protected worksheet when there are locked columns. I want to be able to copy down any formulas from the row above when inserting new rows, but literally only formula not the content of the entire row. On the attached sample when inserting one or multiple rows, I would want to insert blank rows whilst copying the formula in columns N and AA down so the user can make new entries and the rows still sum. Is this possible? This would relate to any and all formula.

    Using
     ActiveCell.EntireRow.Copy
    copies the entire row.

    This code on ThisWorkbook

    'Change the password "pass" to your password (include the apostrophes)
    Const PW = "pass"
    
        Private Sub Workbook_Open()
        For Each sht In ActiveWorkbook.Sheets
            sht.Protect _
            Password:=PW, _
            DrawingObjects:=True, _
            Contents:=True, _
            Scenarios:=True, _
            UserInterfaceOnly:=True
        Next sht
    End Sub
    And this code in a module

    Sub InsertRows()
    
        Dim J, r As Range
        J = Application.InputBox("Type the Number of Rows to be Inserted", , , , , , , 1)
        If J = False Then Exit Sub
        Set r = Selection
    
    Range(r.Offset(1, 0), r.Offset(J, 0)).EntireRow.Insert
    
    End Sub
    
    Sub DeleteRow()
    
    ' Delete a row
    Selection.EntireRow.Delete Shift:=xlUp
    End Sub
    Many thanks
    Attached Files Attached Files
    Last edited by HangMan; 05-24-2015 at 05:43 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Alter existing formula to copy specific cells in row instead of copy entire column
    By painterartist in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-02-2014, 12:42 PM
  2. Replies: 8
    Last Post: 07-08-2013, 06:03 AM
  3. Replies: 3
    Last Post: 01-30-2013, 04:32 PM
  4. Copy Formula:copy the exact same formula in one column
    By Leprechaun in forum Excel General
    Replies: 1
    Last Post: 12-11-2006, 07:46 PM
  5. [SOLVED] Copy/Paste how to avoid the copy of formula cells w/o calc values
    By Dennis in forum Excel General
    Replies: 10
    Last Post: 03-02-2006, 06:50 PM

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