Paste special

[Heine]

Hello everybody

I want to create a paste special button. My VBA is not as strong,
though.

ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

I thought this little bit of code would do the trick. But it fails.

Any ideas?

 

[Guest]

this code copies only one line. I had to change .select to .copy to get it
to work. I added the .END to put the data in the first empty row and added a
..OFFSET so the last line of data was not over writen.

Private Sub CommandButton1_Click()
ActiveCell.Copy

Selection.End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, skipBlanks:=False, Transpose:=False

End Sub

 

[geniusideas]

Hello everybody

I want to create a paste special button. My VBA is not as strong,
though.

ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

I thought this little bit of code would do the trick. But it fails.

Any ideas?

Why don't U use record function available in Excel. Just record the
step.assign to your button.Done

http://microsoft-excel-macro.blogspot.com

 

[Heine]

Hi again

thanks for the reply.
Cannot get it to work though. What I want is this:

I copy a cell - activate the cell in which I want to paste data - and
then run the macro to paste as values.

The above formula return an error.

Any thoughts?

/Heine

 

[Bob Flanagan]

You would be better off just adding the paste special values button
available in View, Toolbars, Customize, Commands. If you do paste special
via a macro, you can not undo. If you the existing button, you can undo.

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Guest]

Bob gave you the best advice. The reason you are having problems is what Bob
alluded to, but it is worse than he said. When you run the macro, the
clipboard is cleared, so you can't do the paste much less undo anything.

 

[Guest]

I have done this in a macro that is assciated with a hot key cntl-a that I
keep in my personal.xls. I works great. I use it everyday. Just delete the a
ActiveCell.Select line. You don't need it. Do the copy, then click on the
cell where you want to paste and hit cntl-a.

SteveK