| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shouldn't this work?
Sub KillIt()
' ' After you have copied a range(i.e., pivot table), this macro ' is used to paste it in inert form, for distribution ' to non-technical users. If Application.CutCopyMode = True Then With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats .PasteSpecial Paste:=xlPasteColumnWidths End With Else: MsgBox ("You have to copy, before you can paste") End If End Sub When I run this, even after copying a range, it still defaults to the ELSE section. The purpose of the macro, is to PASTE, and I want to make sure the user has copied something, before they run the macro. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shouldn't this work?
I don't think "Else" should be followed by a colon ":".
Paul "Jonathan Cooper" wrote in message ... Sub KillIt() ' ' After you have copied a range(i.e., pivot table), this macro ' is used to paste it in inert form, for distribution ' to non-technical users. If Application.CutCopyMode = True Then With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats .PasteSpecial Paste:=xlPasteColumnWidths End With Else: MsgBox ("You have to copy, before you can paste") End If End Sub When I run this, even after copying a range, it still defaults to the ELSE section. The purpose of the macro, is to PASTE, and I want to make sure the user has copied something, before they run the macro. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shouldn't this work?
Actually the colon is an old and little-used syntax for putting multiple
statements on one line, e.g. For i = 1 To 3: Debug.Print i: Next i Try this to fix your problem: If Application.CutCopyMode Then With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats .PasteSpecial Paste:=xlPasteColumnWidths End With Else MsgBox ("You have to copy, before you can paste") End If "PCLIVE" wrote: I don't think "Else" should be followed by a colon ":". Paul "Jonathan Cooper" wrote in message ... Sub KillIt() ' ' After you have copied a range(i.e., pivot table), this macro ' is used to paste it in inert form, for distribution ' to non-technical users. If Application.CutCopyMode = True Then With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats .PasteSpecial Paste:=xlPasteColumnWidths End With Else: MsgBox ("You have to copy, before you can paste") End If End Sub When I run this, even after copying a range, it still defaults to the ELSE section. The purpose of the macro, is to PASTE, and I want to make sure the user has copied something, before they run the macro. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shouldn't this work?
"Jonathan Cooper" wrote in
message ... Sub KillIt() ' ' After you have copied a range(i.e., pivot table), this macro ' is used to paste it in inert form, for distribution ' to non-technical users. If Application.CutCopyMode = True Then With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats .PasteSpecial Paste:=xlPasteColumnWidths End With Else: MsgBox ("You have to copy, before you can paste") End If End Sub When I run this, even after copying a range, it still defaults to the ELSE section. The purpose of the macro, is to PASTE, and I want to make sure the user has copied something, before they run the macro. Don't think CutCopyMode really returns True like that, rather xlCopy, xlCut or False, so your code should be changed to check for False instead, and moving the paste code to the else branch. /impslayer, aka Birger Johansson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shouldn't this work?
That appears to have done the trick. Thanks.
"Charlie" wrote: Actually the colon is an old and little-used syntax for putting multiple statements on one line, e.g. For i = 1 To 3: Debug.Print i: Next i Try this to fix your problem: If Application.CutCopyMode Then With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats .PasteSpecial Paste:=xlPasteColumnWidths End With Else MsgBox ("You have to copy, before you can paste") End If "PCLIVE" wrote: I don't think "Else" should be followed by a colon ":". Paul "Jonathan Cooper" wrote in message ... Sub KillIt() ' ' After you have copied a range(i.e., pivot table), this macro ' is used to paste it in inert form, for distribution ' to non-technical users. If Application.CutCopyMode = True Then With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats .PasteSpecial Paste:=xlPasteColumnWidths End With Else: MsgBox ("You have to copy, before you can paste") End If End Sub When I run this, even after copying a range, it still defaults to the ELSE section. The purpose of the macro, is to PASTE, and I want to make sure the user has copied something, before they run the macro. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shouldn't this work?
VBA put that in there automatically. I deleted it, and VBA put it back in
there again. "PCLIVE" wrote: I don't think "Else" should be followed by a colon ":". Paul "Jonathan Cooper" wrote in message ... Sub KillIt() ' ' After you have copied a range(i.e., pivot table), this macro ' is used to paste it in inert form, for distribution ' to non-technical users. If Application.CutCopyMode = True Then With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats .PasteSpecial Paste:=xlPasteColumnWidths End With Else: MsgBox ("You have to copy, before you can paste") End If End Sub When I run this, even after copying a range, it still defaults to the ELSE section. The purpose of the macro, is to PASTE, and I want to make sure the user has copied something, before they run the macro. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shouldn't this work?
Check XL VBA's help for CutCopyMode. Pay particular attention to the table
that lists the returned values. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sub KillIt() ' ' After you have copied a range(i.e., pivot table), this macro ' is used to paste it in inert form, for distribution ' to non-technical users. If Application.CutCopyMode = True Then With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats .PasteSpecial Paste:=xlPasteColumnWidths End With Else: MsgBox ("You have to copy, before you can paste") End If End Sub When I run this, even after copying a range, it still defaults to the ELSE section. The purpose of the macro, is to PASTE, and I want to make sure the user has copied something, before they run the macro. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to update a column in a work based on another work sheet | New Users to Excel | |||
| how can i automatically generate work order numbers from work orde | Excel Discussion (Misc queries) | |||
| flash object dont work in my excel work sheet | Excel Discussion (Misc queries) | |||
| Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
| Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
Linear Mode
