macro with paste special returns error

[green fox]

Perhaps I'm just stupid today - this seems like it should be simple
enough, but nothing has worked so far. Can anyone tell me what I
missing?

On worksheet one I have run a macro that hides rows, selects a range
and copies visible cells to the clipboard.

On worksheet two I am trying to run a recorded macro that does a paste
special, values and transpose. The recorded macro does not work, I've
tried a number of variations (even changed my security level) and
nothing is getting me any closer.


Here's the code for the macro

test7 Macro
' Macro recorded 3/22/2007 by afox
'
'
Windows("Daily News Column to pages.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

End Sub


here's the result when I run it:


run-time error 1004

"Paste special method of range class failed"


I would appreciate any help, or even just a clue to point me in the
right direction.

Thanks in advance

Andy

 

[Jim Rech]

Runs for me. Make sure nothing is clearing CutoCopyMode.

You might add this before the paste special:

MsgBox (Application.CutCopyMode = xlCopy)

--
Jim
| Perhaps I'm just stupid today - this seems like it should be simple
| enough, but nothing has worked so far. Can anyone tell me what I
| missing?
|
| On worksheet one I have run a macro that hides rows, selects a range
| and copies visible cells to the clipboard.
|
| On worksheet two I am trying to run a recorded macro that does a paste
| special, values and transpose. The recorded macro does not work, I've
| tried a number of variations (even changed my security level) and
| nothing is getting me any closer.
|
|
| Here's the code for the macro
|
| test7 Macro
| ' Macro recorded 3/22/2007 by afox
| '
| '
| Windows("Daily News Column to pages.xls").Activate
| Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
| SkipBlanks _
| :=False, Transpose:=True
|
| End Sub
|
|
| here's the result when I run it:
|
|
| run-time error 1004
|
| "Paste special method of range class failed"
|
|
| I would appreciate any help, or even just a clue to point me in the
| right direction.
|
| Thanks in advance
|
| Andy
|

 

[Tom Ogilvy]

Running the macro clears the clipboard, so there is nothing to paste and you
get an error.

 

[green fox]

Running the macro clears the clipboard, so there is nothing to paste and you
get an error.

--
Regards,
Tom Ogilvy
















- Show quoted text -

Is there a way to assign the clipboard stuff to a variable, or a named
range (in the first macro) so the data could be retreived by the
second?

Andy

 

[Tom Ogilvy]

What I like to do is do the copy and paste as adjacent or as near as adjacent
commands in the code. I don't know what kind of functionality you want to
achieve so it is hard to suggest a workaround. I this is just a generalized
macro to do a pastespecial on whatever is in the clipboard, then I would
suggest using the built in menu button


Go to Tools=>Customize, then select "commands" tab and in the left listbox
(Categories) select "Edit" and in the right listbox (Commands), select "Paste
Values". Drag this to the the toolbar of choice. Close the Customize
Dialog box.