Paste special macro

[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

 

[Don Guillett]

How can it be the "active"cell if you have copied the new range?

 

[Dave Peterson]

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

 

[jack]

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.

 

[jack]

For macro:

Option Explicit
Sub Macro3A()
ActiveCell.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub

I receive error message:
Run time error '1004':
Pastespecial method of Range class failed

What have I done wrong?

 

[Dave Peterson]

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.

 

[jack]

Thanks!!!!
Your second guess was it! I assigned a shortcut key and it worked as it
should.
Thanks again!