+ Reply to Thread
Results 1 to 4 of 4

Copy paste sheets between workbooks

  1. #1
    Registered User
    Join Date
    10-14-2010
    Location
    BC Canada
    MS-Off Ver
    Excel 365
    Posts
    77

    Copy paste sheets between workbooks

    Hi,

    I've got a template I've made. Generally these templates contained things like vlookup, and index/match with references to other sheets in the workbook and other sheets in different workbooks.

    The problem I am having is when I copy a sheet from one workbook to a different work book.
    If that sheet reference a value thats located in a different sheet in workbook number 1 it will still reference that value when it gets copied to workbook number 2.

    As an example

    Workbook one sheet one

    Hi | 200
    Bye | 100


    Workbook one sheet one

    Bye |
    Please Login or Register  to view this content.
    Now I open a new workbook up
    Workbook 2 sheet 1

    Hi | 70
    Bye | 450

    Workbook 2 sheet two
    I copy and past sheet 2 from work book one and I get
    Bye |
    Please Login or Register  to view this content.
    What I want is it to reference the current workbook.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Copy paste sheets between workbooks

    Have you tried to search and replace in formulas? Something like search for [Book1] and replace it with "".
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    10-14-2010
    Location
    BC Canada
    MS-Off Ver
    Excel 365
    Posts
    77

    Re: Copy paste sheets between workbooks

    Yup, that works but it seems clunky. I'm looking for a more stream lined approach. Is there anything I can add to the formula so it doesn't reference the old workbook. Almost like the $ when using cell reference.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Copy paste sheets between workbooks

    If you were using VBA there is an "ActiveWorkbook" method you could use but I don't think you want to go there.

    Example: If you go to the VBA editor and in the immediate area type
    Please Login or Register  to view this content.
    and press enter the name of the active workbook will return.

    I'm not sure you want to go to VBA to do your copy and paste formulas.

    You could also create a macro to search and replace as suggested above.

+ 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