copy only value and not formula
 

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

copy only value and not formula
 

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




"ramzi" wrote:

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

copy only value and not formula
 

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

copy only value and not formula
 

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

"ramzi" wrote:

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