Delete/Copy Paste around Locked Cells

ExcelNoob222

New Member
Joined
Jun 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hi all,

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

ItemCostQtyTotal Cost
A$1.002$2.00
B$2.004$8.00
C$3.006$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?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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

New Member
Joined
Jun 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
amended to allow to paste into more rows at once
error handling included

Replace previous version with code below

VBA Code:
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

New Member
Joined
Jun 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Replace previous version with code below

VBA Code:
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

New Member
Joined
Jun 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
I tried:

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

VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Place in a module like Module1
VBA Code:
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
Shortcut.jpg
 

ExcelNoob222

New Member
Joined
Jun 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Place in a module like Module1
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,105
Messages
5,622,762
Members
415,926
Latest member
jerrynababa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top