Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
copy formula result (text) only - without copying formula | Excel Discussion (Misc queries) | |||
I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) |