Prev: Reference a cell value in a formula
Next: find header then replace header with number of entries below heade
From: xp on 11 Feb 2010 18:55
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...
From: Otto Moehrbach on 11 Feb 2010 19:19
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

"xp" <xp(a)discussions.microsoft.com> wrote in message
news:2E3C7DBC-C127-4368-A501-5267047E2B2E(a)microsoft.com...
> 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...

From: marcus on 11 Feb 2010 19:23
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
From: Jef Gorbach on 11 Feb 2010 19:52
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

From: J_Knowles on 11 Feb 2010 20:20
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,
--
Data Hog


"xp" wrote:

> 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...
 | 
Pages: 1
Prev: Reference a cell value in a formula
Next: find header then replace header with number of entries below heade