Allow paste special only to one worksheet and not to all

[SANDIND]

I have the following code for allowing only Paste special to workbook I am
working,

I have assigned shortcut (ctrl + v) to same. I Thought that same will work
only in one sheet as I have my code in Sheet1 of workbook, but once i use
CtrlV its only pasting Value not only to other sheets but also to other
workbooks, Is there anyway to restrict that to one sheet/workbook.

Code :

Sub MyPasteValues()


If Application.CutCopyMode = False Then
Beep
Else
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
End Sub

Thanks for your help in advance,

PP

 

[Ritwik Shukla]

Try this for paste....

Sheets("Nameof Sheet").Range("LocationofRange").PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[meh2030]

Try this for paste....

Sheets("Nameof Sheet").Range("LocationofRange").PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Try putting your code into events in the ThisWorkbook class. As you
Activate/Deactivate the workbook or SheetChange the worksheets you can
control when to allow xlPasteValues and when to allow pasting.

Matt

 

[Power Loser]

Thanks Matt for your reply,

I am new user to VB, it would be kind of you if you can explain the process
in detail if possible. i.e how to use activate -deactivate option.

Thanks again.

PP

 

[meh2030]

Thanks Matt for your reply,

I am new user to VB, it would be kind of you if you can explain the process
in detail if possible. i.e how to use activate -deactivate option.

Thanks again.

PP

PP,

The following is for Excel 2003. (I don't have Excel 2007 yet, so I'm
not sure if there are any differences). Within VBE there is a
"Project - VBAProject" window (View | Project Explorer). Each
workbook will have a corresponding VBAProject. Each VBAProject will
have a Microsoft Excel Objects folder. When you expand this folder
you will see the "ThisWorkbook" object. If you double click
ThisWorkbook it will open the code window for ThisWorkbook. At the
top of the code window there will be two drop downs. Click the drop
down on the left and select "Workbook." (The default should read
"(General)"). When you select "Workbook" the right drop down should
change from "(Declarations)" to "Open" and the "Workbook_Open" Event
should appear in the code window automatically. If you click on the
right drop down you'll notice that there are a number of Workbook
Events available to you, such as Activate, Deactivate, and
SheetActivate. When you click on an event, the code will
automatically populate itself in the code window. Don't alter the
Private Sub... lines.

Now that I've talked about Worbook Events, the other component you
will need is the .OnKey method. Go to the VBE Help (Help | Help) and
search OnKey. You'll get a good explanation of what this does. In
short, it allows you to set macros to specific keyboard keys, such as
Ctrl + , Ctrl + Shift + , Alt +, Alt + Shift +, etc. Feel free to
change the sheet name (i.e. "Sheet1" to whatever your sheet name is).

I hope this helps. (I really didn't test this, but it should work).

Matt

'----------------------------------------
'Place the Event code in the ThisWorkbook section

Private Sub Workbook_Open()
'set the key if the active sheet is on
''Sheet1 when the workbook is opened
If ActiveSheet.Name = "Sheet1" Then
Application.OnKey "^v", TestMyPaste
End If
End Sub

Private Sub Workbook_Deactivate()
'reset Ctrl + c to its original state
Application.OnKey "^v"
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Debug.Print Sh.Name
If Sh.Name = "Sheet1" Then
'set Ctrl + c to the testMyPaste procedure
Application.OnKey "^v", "TestMyPaste"
Else
'reset Ctrl + c to its original state
Application.OnKey "^v"
End If
End Sub

'----------------------------------------
'Add the following to a Module (Insert | Module)

Sub TestMyPaste()

If Application.CutCopyMode Then
Selection.PasteSpecial Paste:=xlPasteValues
End If

End Sub

 

[Power Loser]

Thanks Matt...

It worked, you were of great help...

Thannks again for you help...

PP