Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow paste special only to one worksheet and not to all
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow paste special only to one worksheet and not to all
Try this for paste....
Sheets("Nameof Sheet").Range("LocationofRange").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ------------- "SANDIND" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow paste special only to one worksheet and not to all
On Mar 6, 3:04*am, Ritwik Shukla
wrote: Try this for paste.... Sheets("Nameof Sheet").Range("LocationofRange").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ------------- "SANDIND" wrote: 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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow paste special only to one worksheet and not to all
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 " wrote: On Mar 6, 3:04 am, Ritwik Shukla wrote: Try this for paste.... Sheets("Nameof Sheet").Range("LocationofRange").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ------------- "SANDIND" wrote: 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow paste special only to one worksheet and not to all
On Mar 6, 12:51*pm, Power Loser <Power
wrote: 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 " wrote: On Mar 6, 3:04 am, Ritwik Shukla wrote: Try this for paste.... Sheets("Nameof Sheet").Range("LocationofRange").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ------------- "SANDIND" wrote: 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 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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow paste special only to one worksheet and not to all
Thanks Matt...
It worked, you were of great help... Thannks again for you help... PP " wrote: On Mar 6, 12:51 pm, Power Loser <Power wrote: 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 " wrote: On Mar 6, 3:04 am, Ritwik Shukla wrote: Try this for paste.... Sheets("Nameof Sheet").Range("LocationofRange").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ------------- "SANDIND" wrote: 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 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet change macro for paste special | Excel Discussion (Misc queries) | |||
paste special excel worksheet range into outlook task body | Excel Programming | |||
Help: Reformatting worksheet: paste special challenge | Excel Discussion (Misc queries) | |||
trying to "paste special" data; why does worksheet object appear? | Excel Discussion (Misc queries) | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming |