copy only value and not formula

[ramzi]

hi,

Below is my macro....
if my data only at A1 to A3, but A4 and A5 still have program (e.g =b4*b5),
how to copy only value (data) without copy the formula inside A4 and A5. So
for the next copy , it will start paste at E4 instead of E6.


Range("A1:A5").Select
Selection.Copy
Range("E1").Select
lastrow = Sheets("sheet1").Cells(Cells.Rows.Count, "E").End(xlUp).Row + 1
Sheets("Sheet1").Range("e" & lastrow).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C7").Select
Application.CutCopyMode = False

rgds
Ramzi

 

[Greg Wilson]

Don't know what you're trying to do, but this does pretty much what your
macro does without the selection and PasteSpecial. Maybe it's a step in the
right direction?

Sub Test()
Dim rw As Long
rw = Cells(Rows.Count, 5).End(xlUp).Row + 1
With Range("A1:A5")
.Value = .Value
Cells(rw, 5).Resize(5, 1).Value = .Value
End With
End Sub

Greg

 

[Greg Wilson]

Sorry, I missed the "Sheet1" qualification. This assumes that the transfer of
data to column E is on a different sheet (Sheet1 in this case):

Sub Test()
Dim rw As Long
rw = Sheets("Sheet1").Cells(Rows.Count, 5).End(xlUp).Row + 1
With Range("A1:A5")
.Value = .Value
Sheets("Sheet1").Cells(rw, 5).Resize(5, 1).Value = .Value
End With
End Sub

Greg

 

[Greg Wilson]

I think I figured out what you meant. This is a second attempt. The code
assumes the source range is hard coded as A1:A5. However, you can change it
to a selected cell range instead. See the commented out line.

Sub Test2()
Dim r As Range, c As Range
Dim rw As Long
rw = Sheets("Sheet1").Cells(Rows.Count, 5).End(xlUp).Row + 1
'Set r = Selection.SpecialCells(xlCellTypeConstants)
Set r = Range("A1:A5").SpecialCells(xlCellTypeConstants)
For Each c In r.Cells
Sheets("Sheet1").Cells(rw, 5).Value = c.Value
rw = rw + 1
Next
Set c = Nothing: Set r = Nothing
End Sub

Greg