pastespecial

[Guest]

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

 

[Norman Jones]

Hi Jeff,

See a reply at your previous post.

 

[Guest]

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

 

[Guest]

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

 

[Norman Jones]

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