1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - copying data and pasting data in a formula cell

Discussion in 'Business Applications' started by pctsvs, Oct 25, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. pctsvs

    pctsvs Thread Starter

    Joined:
    Aug 15, 2005
    Messages:
    74
    I have a spreadsheet that contains formulas. I want a macro that will copy the data only in that cell with the formula and paste only the data in another cell.

    I have been scratching my head for a little bit on this one.

    Thanks
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    If I understand correctly, you can actually avoid using copy altogether.

    Let's say you have the value 50 in A1 and the formula =A1*2 in A2. The following macro would copy A2 to D2:

    Sub Macro1()
    Selection.Copy
    Range("D2").Select
    ActiveSheet.Paste
    End Sub


    but because the formula uses relative references, the resultant formula (in D2) would be =D1*2.

    So what you could do instead -- using a single command only -- is:

    Range("D2").Value = Range("A2").Value

    HTH,
    bomb
     
  3. pctsvs

    pctsvs Thread Starter

    Joined:
    Aug 15, 2005
    Messages:
    74
    Ok that would work for one cell. What if I had multiple cells that I want to take the data from.

    Let's say I have data in B1:B31 and each of the 31 cells contains a formula. Instead of moving data one cell at a time, I want to move all 31 cells at the same time to A1:A31. Would there be a quicker way to do that than to write a macro like so.

    Range("A1").Value = Range("B1").Value
    Range("A2").Value = Range("B2").Value
    And so on for 31 cells.

    I am currently using
    Activecell = Activecell.Offset(0 ,1)
    but I don't want to have to write that down 31 times either.

    Any Ideas.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Well I never tried it before, but:

    Range("A1:A31") = Range("B1:B31").Value

    seems to fit the bill.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/512540

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice