Results 1 to 5 of 5

Copy and Paste Cell Value Only - Not Formula - from one sheet to another sheet

This is a discussion on Copy and Paste Cell Value Only - Not Formula - from one sheet to another sheet within the Excel Questions forums, part of the Question Forums category; I am only newly introduced to VBA code and macros, and am urgently looking for a solution. I would be ...

  1. #1
    New Member
    Join Date
    Jan 2010
    Posts
    3

    Exclamation Copy and Paste Cell Value Only - Not Formula - from one sheet to another sheet

    I am only newly introduced to VBA code and macros, and am urgently looking for a solution. I would be really really grateful for suggestions in finding a solution to the code below!

    The code copies a value from multiples sheets (same range/cell range) and pastes it into the current sheet (ResultsTable)

    The cells being copied contains a formula.

    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)


    Sub cpypaste()
    Dim ws As Worksheet
    x = 0
    For Each ws In Worksheets
    If ws.Name <> "ResultsTable"

    ws.Range("e108").Copy Destination:=Sheets("ResultsTable").Range("d6").Offset(x, 0)
    ws.Range("g111").Copy Destination:=Sheets("ResultsTable").Range("e6").Offset(x, 0)
    ws.Range("g109").Copy Destination:=Sheets("ResultsTable").Range("f6").Offset(x, 0)


    x = x + 0
    End If
    Next ws
    End Sub

  2. #2
    Board Regular sous2817's Avatar
    Join Date
    Feb 2008
    Location
    Raleigh, NC
    Posts
    2,262

    Default Re: Copy and Paste Cell Value Only - Not Formula - from one sheet to another sheet

    Hello and welcome to the board!

    Try use the macro recorder to get the format for pasting values. If you still have problems, post back...
    "Even a blind pig finds an acorn now and again"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    - Search Mr. Excel using Google

    - Search Mr. Excel using Bing

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,979

    Default Re: Copy and Paste Cell Value Only - Not Formula - from one sheet to another sheet

    Try eg:

    Sheets("ResultsTable").Range("d6").Offset(x, 0).Value = ws.Range("e108").Value
    Microsoft MVP - Excel

  4. #4
    New Member
    Join Date
    Jan 2010
    Posts
    3

    Default Re: Copy and Paste Cell Value Only - Not Formula - from one sheet to another sheet

    Super! Thanks so much, it works.
    Quote Originally Posted by Andrew Poulsom View Post
    Try eg:

    Sheets("ResultsTable").Range("d6").Offset(x, 0).Value = ws.Range("e108").Value

  5. #5
    New Member
    Join Date
    Jan 2010
    Posts
    3

    Default Re: Copy and Paste Cell Value Only - Not Formula - from one sheet to another sheet

    Thanks for the tip too!

    Quote Originally Posted by sous2817 View Post
    Hello and welcome to the board!

    Try use the macro recorder to get the format for pasting values. If you still have problems, post back...

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