Delete/Copy Paste around Locked Cells

[ExcelNoob222]

Hi all,

Suppose I have the below data in columns A to D:

Item	Cost	Qty	Total Cost
A	$1.00	2	$2.00
B	$2.00	4	$8.00
C	$3.00	6	$18.00

Column D is a formula and is locked to prevent editing. Column A to C are unlocked to allow for data entry. I want users to be able to copy and paste a row to prevent having to manual key identical data. However because column D is locked I cannot do this. Is there a way to have column D locked, select the entire row (say row 2) copy everything but the locked cell and then paste it into say row 4.

I also want them to have the ability to select the row and press delete, deleting all of the data entry cells in the row but not the locked cell.

My real data is approximately 30 columns with data entry cells scattered. They are not all in a row so selecting columns A to C of a row will not work. Any ideas?

 

[Yongle]

it could be done if you use VBA

Is there a way to have column D locked, select the entire row (say row 2) copy everything but the locked cell and then paste it into say row 4.
My real data is approximately 30 columns with data entry cells scattered. They are not all in a row so selecting columns A to C of a row will not work.
Any ideas?

- are users always pasting into the same columns as the copied from columns?
ie copy A2:Z2 and paste into A4:Z4 (except column D)

- does D2 have the same formula as D4?

I also want them to have the ability to select the row and press delete, deleting all of the data entry cells in the row but not the locked cell

- which is the last column ?

 

[ExcelNoob222]

Hi,

Yes the same columns are always the paste columns. The formulas are the exact same in the column. Except the formula will reference the current row. For example in column D2 the formula is B2*C2. in D4 it would be B4*C4.

The real data goes from columna A to AU. With inputs in columns A-D, N-O, R, U, X, Z, and AB. The rest are locked formulas.

 

[Yongle]

If the WHOLE row is always being copied ...
Test by right-clicking in copyFrom row


right-click on sheet tab \ View Code \ paste code into that window

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim r1 As Long, r2 As Long
    If MsgBox("Copy this row?", vbOKOnly, "") = vbOK Then
        Cancel = True
        r1 = ActiveCell.Row
        r2 = Application.InputBox("Select any cell in PASTE row", "", , , , , , 8).Row
        Me.Unprotect "passWord"
        Rows(r1).Copy Rows(r2)
        Me.Unprotect "passWord"
    End If
End Sub

 

[ExcelNoob222]

If the WHOLE row is always being copied ...
Test by right-clicking in copyFrom row


right-click on sheet tab \ View Code \ paste code into that window

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim r1 As Long, r2 As Long
    If MsgBox("Copy this row?", vbOKOnly, "") = vbOK Then
        Cancel = True
        r1 = ActiveCell.Row
        r2 = Application.InputBox("Select any cell in PASTE row", "", , , , , , 8).Row
        Me.Unprotect "passWord"
        Rows(r1).Copy Rows(r2)
        Me.Unprotect "passWord"
    End If
End Sub

Hi, I am sorry I did not respond sooner, I had some personal things come up.

Thank you so much for this. It works! I am curious is it possible to adjust the code to allow to paste into more rows at once? Also if I hit cancel, I get the debug error message, any way to disable that and just have the macro end?

Thanks again this is really awesome!

 

[Yongle]

amended to allow to paste into more rows at once
error handling included

Replace previous version with code below

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim r1 As Range, r2 As Range
    If MsgBox("Copy this row?", vbOKOnly, "") = vbOK Then
        Cancel = True
        Set r1 = ActiveCell.EntireRow
        On Error GoTo Handling
        Set r2 = Application.InputBox("Select cells in Paste rows", "", , , , , , 8).EntireRow
        Me.Unprotect "passWord"
        r1.Copy r2
        Me.Unprotect "passWord"
    End If
Handling:
    On Error GoTo 0
End Sub

 

[ExcelNoob222]

amended to allow to paste into more rows at once
error handling included

Replace previous version with code below

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim r1 As Range, r2 As Range
    If MsgBox("Copy this row?", vbOKOnly, "") = vbOK Then
        Cancel = True
        Set r1 = ActiveCell.EntireRow
        On Error GoTo Handling
        Set r2 = Application.InputBox("Select cells in Paste rows", "", , , , , , 8).EntireRow
        Me.Unprotect "passWord"
        r1.Copy r2
        Me.Unprotect "passWord"
    End If
Handling:
    On Error GoTo 0
End Sub

Awesome! Any way to assign the macro to a custom hotkey instead of right click?

 

[ExcelNoob222]

I tried:

Sub CopyPasteShortcut()
Application.OnKey "+^{C}", "CopyPasteMacro"
End Sub

Private Sub CopyPasteMacro()
    Dim r1 As Range, r2 As Range
    If MsgBox("Copy this row?", vbOKOnly, "") = vbOK Then
        Cancel = True
        Set r1 = ActiveCell.EntireRow
        On Error GoTo Handling
        Set r2 = Application.InputBox("Select cells in Paste rows", "", , , , , , 8).EntireRow
        Me.Unprotect "pass"
        r1.Copy r2
        Me.Protect "pass"
    End If
Handling:
    On Error GoTo 0
End Sub

But I get a "this macro may not be available in this workbook error"

 

[Yongle]

Place in a module like Module1

Sub CopyRow()
    Dim r1 As Range, r2 As Range
    If MsgBox("Copy this row?", vbOKOnly, "") = vbOK Then
        Set r1 = ActiveCell.EntireRow
        On Error GoTo Handling
        Set r2 = Application.InputBox("Select cells in Paste rows", "", , , , , , 8).EntireRow
        ActiveSheet.Unprotect "passWord"
        r1.Copy r2
        ActiveSheet.Protect "passWord"
    End If
Handling:
    On Error GoTo 0
End Sub

{ALT}{F8} to list macros
Select CopyRow
Click "Options"
insert desired letter next to Ctrl+ and click on OK
with details as below, macro is run with {CTRL} q

 

[ExcelNoob222]

Place in a module like Module1

Sub CopyRow()
    Dim r1 As Range, r2 As Range
    If MsgBox("Copy this row?", vbOKOnly, "") = vbOK Then
        Set r1 = ActiveCell.EntireRow
        On Error GoTo Handling
        Set r2 = Application.InputBox("Select cells in Paste rows", "", , , , , , 8).EntireRow
        ActiveSheet.Unprotect "passWord"
        r1.Copy r2
        ActiveSheet.Protect "passWord"
    End If
Handling:
    On Error GoTo 0
End Sub

{ALT}{F8} to list macros
Select CopyRow
Click "Options"
insert desired letter next to Ctrl+ and click on OK
with details as below, macro is run with {CTRL} q
View attachment 18609

Hmm, if I do it this way, would the macro then work on any sheet? Or can I still limit it to the current sheet.