copy cell values only

  • Thread starter tkraju via OfficeKB.com
  • Start date
T

tkraju via OfficeKB.com

Cells(r,2).Resize(1,2).Copy Cells(2+off,"F")
The above code copying cell values and formulas.My requirement is to copy
cell values(contents) only.What changes I need to do the above code line to
copy cell contents only.
 
T

TUNGANA KURMA RAJU

Patrick,
I changed the code,but I didn't get desired results.this has permanently cut
the values from the column 2 and pasted the values in column F
 
P

Patrick Molloy

well the "F" was in your code as the target cell

the correct syntax is


with Source
Target.Resize( .Rows.Count, .Cols.Count ).Value = .Value
End With

where both Source and Target are defined and set as ranges
 
T

TUNGANA KURMA RAJU

Sub extractData()
Dim sName As String
Dim dDate As Date
Dim Ammount As Double
Dim LastRow As Long
Dim off As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
sName = Range("D1").Value
dDate = Range("E1").Value
For r = 2 To LastRow
If Format(Cells(r, 2), "mm-yy") = Format(dDate, "mm-yy") Then
If Cells(r, 1).Value = sName Then
Cells(r, 2).Resize(1, 2).Copy Cells(2 + off, "K")
Cells(r, 6).Resize(1, 2).Copy Cells(2+ off, "M")
off = off + 1
End If
End If
Next
End Sub

Well this is my code.I am getting problem in that second line
copying.Cells(r, 6) means Col F and G values be copied to Col M and N,where
as Col F and G values formula based result values,thus I am getting different
values in Col M and N.
I need Contents of Col F and G are to be copied to Col M and N.
 
P

Per Jessen

Hi

I think this is what you need:

Cells(r,2).Resize(1,2).Copy
Cells(2+off,"F").PasteSpecial xlPasteValues

Regards,
Per
 
T

TUNGANA KURMA RAJU

Thank you Per Jessen,
this code is yours,I think you have not seen my reply in OfficeKb.
this problem is almost over,now it is pasting values only ,the bug is last
row cells still in selected state, and last source(copy) cells still blinking
around.

Per Jessen said:
Hi

I think this is what you need:

Cells(r,2).Resize(1,2).Copy
Cells(2+off,"F").PasteSpecial xlPasteValues

Regards,
Per
Click to expand...
 
P

Per Jessen

Hi

Haven't seen your last reply, will look at it tomorrow.

Add this after the "Next" statement to turn off "marching ants":

Application.CutCopyMode=False

Regards,
Per

TUNGANA KURMA RAJU said:
Thank you Per Jessen,
this code is yours,I think you have not seen my reply in OfficeKb.
this problem is almost over,now it is pasting values only ,the bug is last
row cells still in selected state, and last source(copy) cells still
blinking
around.
Click to expand...
 
Top