Copy values only, sheet1 to sheet2 without Selection
 

Hi all

Using Office Excel 2003 Sp1

Is it possible to copy a 3 cell range from €œSheet1€� to €œSheet2€� without
selecting €œSheet2€� using Destination?
My problem is trying to locate the first blank cell in column B on €œSheet2€�
and then pasting only the values of the 3 cells.

Sub CopyValu()
Dim dt as Worksheet
Set dt = Worksheets(€œData1€�)

Range(€œB13:D13€�).Copy Destination:=dt.Cells(rows.Count,
€œB€�).End(xlUp).Offset(1, 0)

Can a paste values only be added to this line and if so, please how?

I started out with recorded code and got as far as lines below, but am now
stumped. Can not join all lines together.

[C13:D13].Copy
Sheets("Data1").Select
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:= _
xlPasteValuesAndNumberFormats, Operation:= xlNone, SkipBlanks:= _
False, Transpose:=False

Any help greatly appreciated.

--
Thank you

Regards

Bob C

Copy values only, sheet1 to sheet2 without Selection
 

Sub CopyValue()
Dim dt As Worksheet
Set dt = Worksheets("Data1")

Range("B13:D13").Copy
dt.Cells(Rows.Count, "B").End(xlUp) _
.Offset(1, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

End Sub



--
Regards,
Tom Ogilvy


Can a paste values only be added to this line and if so, please how?

I started out with recorded code and got as far as lines below, but am now
stumped. Can not join all lines together.

[C13:D13].Copy
Sheets("Data1").Select
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:= _
xlPasteValuesAndNumberFormats, Operation:= xlNone, SkipBlanks:= _
False, Transpose:=False



"Robert Christie" wrote in message
...
Hi all

Using Office Excel 2003 Sp1

Is it possible to copy a 3 cell range from "Sheet1" to "Sheet2" without
selecting "Sheet2" using Destination?
My problem is trying to locate the first blank cell in column B on
"Sheet2"
and then pasting only the values of the 3 cells.

Sub CopyValu()
Dim dt as Worksheet
Set dt = Worksheets("Data1")

Range("B13:D13").Copy Destination:=dt.Cells(rows.Count,
"B").End(xlUp).Offset(1, 0)

Can a paste values only be added to this line and if so, please how?

I started out with recorded code and got as far as lines below, but am now
stumped. Can not join all lines together.

[C13:D13].Copy
Sheets("Data1").Select
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:= _
xlPasteValuesAndNumberFormats, Operation:= xlNone, SkipBlanks:= _
False, Transpose:=False

Any help greatly appreciated.

--
Thank you

Regards

Bob C

Copy values only, sheet1 to sheet2 without Selection
 

If you wanted to copy an arbitrary three cell range w/o using select
from one sheet to another, you could do something like this.

Worksheets("-Sheet1-").Range("A1:C1").Copy
Worksheets("-Sheet2-").Range("A1:C1"). PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

This would copy those values regardless of what sheet you're current
looking at (could even be Sheet3 for example)

Tell me if this is not what you meant..


--
ob3ron02
------------------------------------------------------------------------
ob3ron02's Profile: http://www.excelforum.com/member.php...o&userid=15450
View this thread: http://www.excelforum.com/showthread...hreadid=270772

Copy values only, sheet1 to sheet2 without Selection
 

Thanks once again Tom

I nearly had it <g

Regards Bob C


"Tom Ogilvy" wrote:

Sub CopyValue()
Dim dt As Worksheet
Set dt = Worksheets("Data1")

Range("B13:D13").Copy
dt.Cells(Rows.Count, "B").End(xlUp) _
.Offset(1, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

End Sub



--
Regards,
Tom Ogilvy


Can a paste values only be added to this line and if so, please how?

I started out with recorded code and got as far as lines below, but am now
stumped. Can not join all lines together.

[C13:D13].Copy
Sheets("Data1").Select
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:= _
xlPasteValuesAndNumberFormats, Operation:= xlNone, SkipBlanks:= _
False, Transpose:=False



"Robert Christie" wrote in message
...
Hi all

Using Office Excel 2003 Sp1

Is it possible to copy a 3 cell range from "Sheet1" to "Sheet2" without
selecting "Sheet2" using Destination?
My problem is trying to locate the first blank cell in column B on
"Sheet2"
and then pasting only the values of the 3 cells.

Sub CopyValu()
Dim dt as Worksheet
Set dt = Worksheets("Data1")

Range("B13:D13").Copy Destination:=dt.Cells(rows.Count,
"B").End(xlUp).Offset(1, 0)

Can a paste values only be added to this line and if so, please how?

I started out with recorded code and got as far as lines below, but am now
stumped. Can not join all lines together.

[C13:D13].Copy
Sheets("Data1").Select
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:= _
xlPasteValuesAndNumberFormats, Operation:= xlNone, SkipBlanks:= _
False, Transpose:=False

Any help greatly appreciated.

--
Thank you

Regards

Bob C

Copy values only, sheet1 to sheet2 without Selection
 

Hi ob3

No not quite the answer I was after.
My main problem was trying to join the code together and still locate the
first blank cell in second sheet. Apparently from Tom Ogilvy's post it was
all in front of me, just had to join it. A case of not seeing the forest for
the trees.

Thanks for your post

Regards Bob C

"ob3ron02" wrote:


If you wanted to copy an arbitrary three cell range w/o using select
from one sheet to another, you could do something like this.

Worksheets("-Sheet1-").Range("A1:C1").Copy
Worksheets("-Sheet2-").Range("A1:C1"). PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

This would copy those values regardless of what sheet you're current
looking at (could even be Sheet3 for example)

Tell me if this is not what you meant..


--
ob3ron02
------------------------------------------------------------------------
ob3ron02's Profile: http://www.excelforum.com/member.php...o&userid=15450
View this thread: http://www.excelforum.com/showthread...hreadid=270772