+ Reply to Thread
Results 1 to 7 of 7

help copying over vlookup formulas without creating link

  1. #1
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,120

    help copying over vlookup formulas without creating link

    I have a spreadsheet that contains a roster of names and next to each name is a vlookup. Whenever I update the roster I run a macro that copies the updated roster to several of my employee's own workbooks. Worked fine except now that I've added the vlookup the formula points them all back to my workbook. Only method I know would be do a find/replace that exact text out. It will work but I just tried it on one workbook and it took 5 seconds. I need to do this to ten workbooks.

    I've uploaded what my workbook. My team looks the same as well.



    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 2010
    Posts
    991

    Re: help copying over vlookup formulas without creating link

    I think rather than copying, you want to just tell it to make the formula the same

    Please Login or Register  to view this content.
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

  3. #3
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,120

    Re: help copying over vlookup formulas without creating link

    If I add this find/replace code into my macro it doesn't work. it does if I run it separately.

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,120

    Re: help copying over vlookup formulas without creating link

    Thank you. that might work, but I can't put that formula in every cell. Does your code only replace formulas? I have other formulas on my worksheet in addition to the vlookup.

  5. #5
    Valued Forum Contributor
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 2010
    Posts
    991

    Re: help copying over vlookup formulas without creating link

    I mean code like this - see if it works
    i also got rid of the date copy and paste that seemed like an odd way of doing it.

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,120

    Re: help copying over vlookup formulas without creating link

    Ok magic man. how did that work? Does that just copy any and all formulas on my sheet? Doesn't matter what formula?

  7. #7
    Valued Forum Contributor
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 2010
    Posts
    991

    Re: help copying over vlookup formulas without creating link

    yes, it sets the formula to be the same (effectively copying them) and doesn't care what formula u have in there
    ie range A formula = range B formula

    Note I am working specifically with the range Range("A1:AB70") as per your example, you can extend that as needed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1