Shouldn't this work?

[Guest]

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.

 

[PCLIVE]

I don't think "Else" should be followed by a colon ":".

Paul

 

[Guest]

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

 

[impslayer]

message news:[email protected]...

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

 

[Guest]

That appears to have done the trick. Thanks.

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

 

[Guest]

VBA put that in there automatically. I deleted it, and VBA put it back in
there again.

 

[Tushar Mehta]

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

 

[Guest]

Thank you.

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