I'm Paste values stupid

[xp]

I'm trying to do something very simple. I go to a sheet and select a range
and click copy to copy a range. I switch to another workbook and click a
button. The code attached to the button should paste values into the active
sheet starting at cell "A3".

It's ridiculous because this should be easy; I even recorded a macro to do
this and the recording fails. Microsoft hasn't improved this yet?????

Here is some of the code I've tried:

ActiveSheet.Range("A3").PasteSpecial Paste:=xlPasteValues
Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("A3").PasteSpecial xlPasteValues
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Any help appreciated...

 

[Otto Moehrbach]

You say "it fails". What does it do? Do you get an error message? If so,
what does it say? The first line of your code should do what you want. I
use such code in a lot of my projects. Never had a problem. I just placed
your first line of code in a macro (by itself) and it works just fine. I
have 2007. HTH Otto

 

[marcus]

Hi

When you copy a range then press a button in excel (any button) the
copy you just made will be lost. So the result will be nothing
happening or you code bugging out as there is nothing to paste as your
copy was lost.

If you copy and go to the sheet you want to paste it to there is a
pastespecials values button in the customed menu which does just
this.

Right click on your toolbar -Customise, Commands, Edit and choose the
pase values button.

Alternatively if you want a vb solution also, you have to do the copy
and paste together.

Worksheets("Sheet1").Range("A1:A4").Copy 'example range to copy
Worksheets("Sheet2").Range("A3").PasteSpecial xlPasteValues

Take care

Marcus

 

[Jef Gorbach]

You're close, but none of the lines change sheets so you're simply
copy/pasting the results back where you copied them from. If you
really want to go the copy/paste value route, try this:

Range("A3:B5").Copy
Sheets("Sheet2").Range("A3").PasteSpecial Paste:=xlPasteValues

however a faster/more direct alternative would be:
Sheets("Sheet2").Range("A3:B5").Value = Range("A3:B5").Value

 

[J_Knowles]

Sub Macro1()
'source workbook
Workbooks("delete_blank_rowsColB.xlsm").Worksheets("Sheet1").Activate
ActiveSheet.Range("A3").Copy
'destination workbook
Windows("Book3").Activate 'unsaved workbook
ActiveSheet.Range("A3").PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("A3").PasteSpecial Paste:=xlPasteFormats

End Sub

HTH,