Results 1 to 9 of 9

VBA Copy and Paste Cell Value Only - Not Formula

This is a discussion on VBA Copy and Paste Cell Value Only - Not Formula within the Excel Questions forums, part of the Question Forums category; I am having problems with the code listed below. The syntax is fine, but it returns a value of #N/A. ...

  1. #1
    New Member
    Join Date
    Apr 2003
    Posts
    39

    Default VBA Copy and Paste Cell Value Only - Not Formula

    I am having problems with the code listed below. The syntax is fine, but it returns a value of #N/A. The code copies a value from a second sheet and pastes it into the current sheet.

    The cell being copied derives its value from a VLOOKUP function on a different sheet. I think this may be creating the problem.

    Is there a way to copy a cell's numeric value only and not the formula? Or maybe paste the value only (like the Paste Special - Values Only menu item)?
    ---------------------------------------------
    With Worksheets("Daily Calcs")
    For i = 2 To 360
    If Worksheets("Positions").Cells(i, 20).Value = "RED LINE" Then
    Set rng6 = .Range("A3").End(xlDown).Offset(0, 41)
    rng6.Copy
    ActiveSheet.Paste Destination:=Worksheets("Positions").Range("W2")
    End If
    Next i
    End With

    Thanks,
    Will

  2. #2
    Board Regular kskinne's Avatar
    Join Date
    Feb 2002
    Location
    USA
    Posts
    1,263

    Default Re: VBA Copy and Paste Cell Value Only - Not Formula

    to copy a formula result as a value instead of the formula, instead of '.Paste', use '.PasteSpecial'

    just replace your line of code:
    ActiveSheet.Paste Destination:=Worksheets("Positions").Range("W2")

    with this line:
    Worksheets("Positions").Range("W2").PasteSpecial xlPasteValues

    see the excel vba help file for the use of the PasteSpecial method. it's pretty straightforward.

    hth
    kevin

  3. #3
    Board Regular kskinne's Avatar
    Join Date
    Feb 2002
    Location
    USA
    Posts
    1,263

    Default Re: VBA Copy and Paste Cell Value Only - Not Formula

    if i could make one more suggestion, instead of using:

    Set rng6 = .Range("A3").End(xlDown).Offset(0, 41)
    rng6.Copy
    ActiveSheet.Paste Destination:=Worksheets("Positions").Range("W2")

    you could use:

    Worksheets("Positions").Range("W2").value = .Range("A3").End(xlDown).Offset(0, 41).value

    which will accomplish the same thing - plus it will allow your code to run more quickly by avoiding the use of copy and paste

    kevin

  4. #4
    New Member
    Join Date
    Apr 2003
    Posts
    39

    Default Re: VBA Copy and Paste Cell Value Only - Not Formula

    It worked! (Why would I doubt the mighty Kevin?) And thanks for this tip on speeding up the code.

  5. #5
    Board Regular
    Join Date
    Aug 2010
    Posts
    70

    Default Re: VBA Copy and Paste Cell Value Only - Not Formula

    I'm trying to copy a cell from one worksheet to the other in the same column, but I want to copy the values. I've tried reviewing both this and the PasteSpecial, but I'm doing something incorrect with the destination...please help.

    FoundCell.Cells.EntireColumn.Copy ActiveWorkbook.Sheets(2).Columns(FoundCell.Column) 'this works

    FoundCell.EntireColumn.PasteSpecial xlPasteValues ActiveWorkbook.Sheets(2).Columns(FoundCell.Column) 'this fails


  6. #6
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: VBA Copy and Paste Cell Value Only - Not Formula

    Will, I suggest you start a new thread and provide more info as to your layout. Also, post the code leading up to the FoundCell. It is also highly likely you don't need to copy the ENTIRECOLUMN.

    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  7. #7
    Board Regular
    Join Date
    Aug 2010
    Posts
    70

    Default Re: VBA Copy and Paste Cell Value Only - Not Formula


  8. #8
    New Member
    Join Date
    Sep 2011
    Posts
    1

    Default Re: VBA Copy and Paste Cell Value Only - Not Formula

    I am using following coding to insert from one sheet to another but i want to have only cell values not the formula of cell. can you please suggest
    Sheets("INS").Select
    Rows("1:13").Select
    Selection.Copy
    Sheets("VW250").Select
    ActiveCell.Select
    Selection.Insert Shift = xlDown
    Application.CutCopyMode = False

  9. #9
    Board Regular
    Join Date
    Jun 2011
    Location
    Perth, Western Australia
    Posts
    56

    Default Re: VBA Copy and Paste Cell Value Only - Not Formula

    Dim lr As Long, i As Long
    With Sheets("DAILY LOG")
    lr = .Range("G" & Rows.Count).End(xlUp).Row
    For i = 19 To lr
    With .Range("G" & i)
    If .Value = "OS" Then .Offset(, -2).Resize(, 1).Copy Destination:=Sheets("Delay Duration").Range("A" & Rows.Count).End(xlUp).Offset(1)
    If .Value = "OD" Then .Offset(, -2).Resize(, 1).Copy Destination:=Sheets("Delay Duration").Range("B" & Rows.Count).End(xlUp).Offset(1)
    If .Value = "NSP" Then .Offset(, -2).Resize(, 1).Copy Destination:=Sheets("Delay Duration").Range("C" & Rows.Count).End(xlUp).Offset(1)
    If .Value = "SL" Then .Offset(, -2).Resize(, 1).Copy Destination:=Sheets("Delay Duration").Range("D" & Rows.Count).End(xlUp).Offset(1)
    If .Value = "ULO" Then .Offset(, -2).Resize(, 1).Copy Destination:=Sheets("Delay Duration").Range("E" & Rows.Count).End(xlUp).Offset(1)
    If .Value = "ULF" Then .Offset(, -2).Resize(, 1).Copy Destination:=Sheets("Delay Duration").Range("F" & Rows.Count).End(xlUp).Offset(1)
    If .Value = "OT" Then .Offset(, -2).Resize(, 1).Copy Destination:=Sheets("Delay Duration").Range("G" & Rows.Count).End(xlUp).Offset(1)
    End With
    Next i
    End With

    This code copies and pastes the formulas whereas I only want the values to be pasted.

    Is it possible to do this by altering the Destination slightly?

    Thanks

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com