pastespecial
 

Hi,
Can someone please help me with the following task:
I am trying to copy a block of cells from sheet1 (starting from
Cell("B1")) and ending on the last cell (most right, most down cell, which
may vary on each execution), paste only values into sheet2.


Thanks,
Jeff

pastespecial
 

Hi Jeff,

See a reply at your previous post.


---
Regards,
Norman



"Jeff" wrote in message
...
Hi,
Can someone please help me with the following task:
I am trying to copy a block of cells from sheet1 (starting from
Cell("B1")) and ending on the last cell (most right, most down cell, which
may vary on each execution), paste only values into sheet2.


Thanks,
Jeff

pastespecial
 

Just Change the range's you want.
Start at B1:
End at R845


Sub Macro2()
'

'
Range("B1:R845").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
End Sub

DJ

"Jeff" wrote:

Hi,
Can someone please help me with the following task:
I am trying to copy a block of cells from sheet1 (starting from
Cell("B1")) and ending on the last cell (most right, most down cell, which
may vary on each execution), paste only values into sheet2.


Thanks,
Jeff

pastespecial
 

Public Sub Tada()
Dim rngToCopy As Range

Set rngToCopy = Sheets("Sheet1").Range("B1",
Range("B1").SpecialCells(xlCellTypeLastCell))
rngToCopy.Copy

Sheets("Sheet2").Select
Sheets("Sheet2").Range("B1").PasteSpecial xlPasteValues

Set rngToCopy = Nothing
End Sub


"Jeff" wrote:

Hi,
Can someone please help me with the following task:
I am trying to copy a block of cells from sheet1 (starting from
Cell("B1")) and ending on the last cell (most right, most down cell, which
may vary on each execution), paste only values into sheet2.


Thanks,
Jeff

pastespecial
 

Hi Jeff,

I missed your paste values addendum.

Try:

Sub Tester()
Dim rng As Range
With ActiveWorkbook.Sheets("Sheet1")
Set rng = .Cells(1, Columns.Count).End(xlToLeft)
Set rng = Cells(Rows.Count, rng.Column).End(xlUp)
Range(.Range("B1"), rng).Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False
End Sub

---
Regards,
Norman



"Jeff" wrote in message
...
Hi,
Can someone please help me with the following task:
I am trying to copy a block of cells from sheet1 (starting from
Cell("B1")) and ending on the last cell (most right, most down cell, which
may vary on each execution), paste only values into sheet2.


Thanks,
Jeff