Copy values only, sheet1 to sheet2 without Selection

[Guest]

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(“B1313â€).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.

[C1313].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

 

[Tom Ogilvy]

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

Range("B1313").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.

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

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("B1313").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.

[C1313].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

 

[ob3ron02]

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..

 

[Guest]

Thanks once again Tom

I nearly had it <g>

Regards Bob C

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

Range("B1313").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.

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

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("B1313").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.

[C1313].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

 

[Guest]

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