PASTE SPECIAL w/ Macro

J

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
 
J

Jase

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

thanks
 
D

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
 
J

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

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
 
J

Jase

Thanks a ton Dave, your'e the man.

Dave Peterson said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

LOOPING multiple ranges 1
PASTE SPECIAL 1
Macro change - help please 3
Add to existing macro 3
Match style 0
Copy data from one sheet to another 0
Prompt for Start Row 2
Auto Autofill 2

Top