PASTE SPECIAL w/ Macro

[Jase]

I can not figure out how to get this code to paste special the values only
instead of the formula. Any suggestions?


Sub CopyFindPaste()



Dim RngToCopy As Range
Dim DestCell As Range

With Worksheets("Sheet1")
Set RngToCopy = .Range("O25:W33")
End With

With Worksheets("Sheet1")
Set DestCell = .Range("AF3")
End With

Do
If IsEmpty(DestCell.Value) Then
Exit Do
Else
Set DestCell = DestCell.Offset(10, 0)
End If
Loop

RngToCopy.Copy _
Destination:=DestCell



End Sub

 

[Don Guillett]

The macro recorder is your friend. Try it while doing a copy/paste special

 

[Jase]

But that does not help me with the way I have this setup using a loop function.

thanks

 

[Don Guillett]

Exactly what, in detail, are you trying to do?

 

[Dave Peterson]

RngToCopy.Copy _
Destination:=DestCell
becomes
RngToCopy.Copy
destcell.pastespecial paste:=xlpastespecial

or you could use:

with rngtocopy
destcell.resize(.rows.count,.columns.count).value = .value
end with

 

[Jase]

I am copying a group of cells with values derived from formulas and I am
pasting them to a certain area. If that area I am pasting them to is not
blank it loops down to the next availabe area and pastes them. However it is
pasting the formulas and I just want the static value from the formulas.

 

[Dave Peterson]

Oops. A typo!

RngToCopy.Copy _
Destination:=DestCell
becomes
RngToCopy.Copy
destcell.pastespecial paste:=xlpasteValues

or you could use:

with rngtocopy
destcell.resize(.rows.count,.columns.count).value = .value
end with

 

[Jase]

Thanks a ton Dave, your'e the man.

Oops. A typo!

RngToCopy.Copy _
Destination:=DestCell
becomes
RngToCopy.Copy
destcell.pastespecial paste:=xlpasteValues

or you could use:

with rngtocopy
destcell.resize(.rows.count,.columns.count).value = .value
end with