Allow some column to copy and paste

Abdulkhadar

Board Regular
Joined
Nov 10, 2013
Messages
143
Office Version
  1. 2010
Platform
  1. Windows
Hi Excel Experts,
In the protected sheet I want to allow user only copy and paste at column C and D, not to allow cut and paste. In this sheet some columns (Except C & D) are having data validation and some columns are formula. but i want to allow user to only copy and paste data from other sources at column C and D.

Thanks in advance.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
VBA is required to do what you want

For my understanding
1 is Cut&Paste allowed elsewhere in the same sheet ? (in any other columns except C & D?)
2 does your workbook contain macros?
3 does your workbook contain only one sheet?
 

Abdulkhadar

Board Regular
Joined
Nov 10, 2013
Messages
143
Office Version
  1. 2010
Platform
  1. Windows
VBA is required to do what you want

For my understanding
1 is Cut&Paste allowed elsewhere in the same sheet ? (in any other columns except C & D?)
2 does your workbook contain macros?
3 does your workbook contain only one sheet?
Thanks for reply,
Yes Sir I want VBA,
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
What are the answers to my questions?
 

Abdulkhadar

Board Regular
Joined
Nov 10, 2013
Messages
143
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

What are the answers to my questions?
Sorry sir,
There are other sheets are there, I want that VBA work in Sheet1 only. Sheet1 locked cells are protected. only from Column C15 & D15 are unlocked. From column E15 and other columns have set as data validation.(data validation columns are also unlocked). I want to allow to user copy and paste data from other excel workbook or source at Column C & D from C15 and D15.
I Think it is clear.
Thanks for your patience and cool response,
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Place this in Sheet1 code window and test everything that user is allowed to do in that worksheet

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C15:D" & Rows.Count)) Is Nothing Then
        If Application.CutCopyMode = 2 Then
            Application.CutCopyMode = 0
            MsgBox "Cut & Paste not permitted here" & vbCr & "use Copy  & Paste", vbExclamation, "STOP"
        End If
    Else
        Application.CutCopyMode = 0
    End If
End Sub

NOTE
I have kept the macro simple - so we may need to amend it if your requirements are more complex
 

Abdulkhadar

Board Regular
Joined
Nov 10, 2013
Messages
143
Office Version
  1. 2010
Platform
  1. Windows
Place this in Sheet1 code window and test everything that user is allowed to do in that worksheet

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C15:D" & Rows.Count)) Is Nothing Then
        If Application.CutCopyMode = 2 Then
            Application.CutCopyMode = 0
            MsgBox "Cut & Paste not permitted here" & vbCr & "use Copy  & Paste", vbExclamation, "STOP"
        End If
    Else
        Application.CutCopyMode = 0
    End If
End Sub

NOTE
I have kept the macro simple - so we may need to amend it if your requirements are more complex

Wow Nice. I've got what I need. Thanks once again
 

Abdulkhadar

Board Regular
Joined
Nov 10, 2013
Messages
143
Office Version
  1. 2010
Platform
  1. Windows
Place this in Sheet1 code window and test everything that user is allowed to do in that worksheet

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C15:D" & Rows.Count)) Is Nothing Then
        If Application.CutCopyMode = 2 Then
            Application.CutCopyMode = 0
            MsgBox "Cut & Paste not permitted here" & vbCr & "use Copy  & Paste", vbExclamation, "STOP"
        End If
    Else
        Application.CutCopyMode = 0
    End If
End Sub

NOTE
I have kept the macro simple - so we may need to amend it if your requirements are more complex
Hello Sir,
I want to allow users to copy and paste also from Column F and M from 15th row. where can I change the value in your VB.

Thanks in advance.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim uRng As Range, r As Long
    r = Me.Rows.Count - 14
    Set uRng = Union(Range("C15:D15").Resize(r), Range("F15").Resize(r), Range("M15").Resize(r))

    If Not Intersect(Target, uRng) Is Nothing Then
        If Application.CutCopyMode = 2 Then
            Application.CutCopyMode = 0
            MsgBox "Cut & Paste not permitted here" & vbCr & "use Copy  & Paste", vbExclamation, "STOP"
        End If
    Else
        Application.CutCopyMode = 0
    End If
End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,295
Messages
5,641,401
Members
417,208
Latest member
wendy823

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