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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jase" wrote in message
...
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

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

thanks

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jase" wrote in message
...
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jase" wrote in message
...
But that does not help me with the way I have this setup using a loop
function.

thanks

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jase" wrote in message
...
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

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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jase" wrote in message
...
But that does not help me with the way I have this setup using a loop
function.

thanks

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jase" wrote in message
...
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

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 wrote:

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

--

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


Dave Peterson wrote:

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 wrote:

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

--

Dave Peterson

--

Dave Peterson

Thanks a ton Dave, your'e the man.

"Dave Peterson" wrote:

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


Dave Peterson wrote:

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 wrote:

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

--

Dave Peterson

--

Dave Peterson