Paste special macro

  • Thread starter Thread starter jack
  • Start date Start date
J

jack

How do I change the first three lines of the macro below so that the paste
special statement will execute to the active cell of a separate worksheet
after making the manual copy selection ?

Sub Macro3()
Range("B6:H6").Select
Selection.Copy
Range("L6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
End Sub
 
If you're doing the .copy in code:

Option Explicit
Sub Macro3A()
Range("B6:H6").Copy
Worksheets("sheet2").Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub

If you've already copied the range and want to paste to the activecell (you
already changed to the other sheet manually):

Option Explicit
Sub Macro3A()
ActiveCell.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub
 
What I am referring to is after manually copying a series of cells in one
spreadsheet, using paste special macro to paste the copied info to any
"active" cell in another spreadsheet.
I am thinking that I need to replace the 1st three lines of the recorded
macro to do this.
 
For macro:
Option Explicit
Sub Macro3A()
ActiveCell.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub
Click to expand...
I receive error message:
Run time error '1004':
Pastespecial method of Range class failed

What have I done wrong?
 
Could be lots of things.

My first guess is that you didn't have anything copied (and ready to be pasted).

My second guess is that you went through the Tools|macro|macros dialog. That's
enough to kill the clipboard. Maybe you could use the same
tools|macro|macro|options to assign a shortcut key.

My third guess is that your receiving worksheet is protected.
 
Thanks!!!!
Your second guess was it! I assigned a shortcut key and it worked as it
should.
Thanks again!
 
Back
Top