Results 1 to 4 of 4

Thread: excel vba insert row and copy formulas with shortcuts

  1. #1
    Seeker Kanita's Avatar
    Join Date
    Sep 2020
    Posts
    7
    Articles
    0
    Excel Version
    Excel for Mac

    Cool excel vba insert row and copy formulas with shortcuts



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there,

    I have a file with protected worksheets. Some cells (with formulas) are protected, some are not. Because of protection of some cells my colleagues can not insert a row and copy the one above. That's why I want to use a macro. My solution was the following:

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Updateby Extendoffice 20160725
        Cancel = True
        ActiveSheet.Protect Password:="XXXX", UserInterfaceOnly:=True
        Target.Offset(1).EntireRow.Insert
        Target.EntireRow.Copy Target.Offset(1).EntireRow
        On Error Resume Next
        Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
    End Sub
    It works, but double click is something, my colleagues want to have for other reasons. I wanted to change it for a shortcut, like Ctrl+D. But because I am not a professional I can not do it myself. Could someone help me to fix the problem?

    Thanks a lot!!!
    Last edited by p45cal; 2020-10-03 at 02:54 PM. Reason: added code tags

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    812
    Articles
    0
    Excel Version
    Excel 2010
    You could put the same coding into a macro in a standard module instead of using the worksheet double click event.
    Use activecell instead of target and assign the Ctrl+D from the macro dialogue that appears when you hit F8.

  3. #3
    Acolyte Bernie's Avatar
    Join Date
    Feb 2018
    Location
    Suburban NY
    Posts
    34
    Articles
    0
    Excel Version
    Excel 2016 (Win)
    You can do it yourself. Remove your code from the worksheet's codemodule and put this code into a standard codemodule:

    Code:
    Sub InsertRow()
       Selection.Cells(1, 1).EntireRow.Copy
        Selection.Cells(1, 1).Offset(1).EntireRow.Insert
        On Error Resume Next
        Selection.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
    End Sub
    then, on the Developer tab of the Ribbon (you may need to right-click the ribbon and choose "Customize the Ribbon" to select the "Developer" tab) click the Macros button, then select the workbook and the sub InsertRow, and assign it a shortcut key using the Options... dialog as show here:
    Click image for larger version. 

Name:	Capture.JPG 
Views:	56 
Size:	88.7 KB 
ID:	10034

    PS The Protect line in your original code only needs to be run once - after that, the sheet can be modified by VBA code without Unprotect/Protect
    Last edited by Bernie; 2020-09-30 at 07:12 PM.
    Bernie Deitrick
    MS Excel MVP 2001-2010

  4. #4
    Seeker Kanita's Avatar
    Join Date
    Sep 2020
    Posts
    7
    Articles
    0
    Excel Version
    Excel for Mac
    Thank you soooooo much! Ups, I did it!

Tags for this Thread

Posting Permissions

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