Copying a formula from one Excel worbook to another Excel workbook
Results 1 to 5 of 5

Thread: Copying a formula from one Excel worbook to another Excel workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2004
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copying a formula from one Excel worbook to another Excel workbook

    I recorded a macro that copies a formula from workbook1 to workbook2. When I checked the formula in workbook2, I noticed that it had the reference to workbook1. I do not want the formula in workbook2 to reference workbook1 in the formula. Is there a way to avoid referencing workbook1 in the formula? I tried everything but without success.
    Thanks

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Posts
    277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying a formula from one Excel worbook to another Excel workbook

    Can you post the code you are using, and what the formula in workbook1 reads?
    My favorite Excel Add-in:= Nutilities

  3. #3
    Board Regular
    Join Date
    Dec 2004
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying a formula from one Excel worbook to another Excel workbook

    Here is the code

    Sub Schedule()
    '
    '
    '

    '
    Windows("Workbook1.xlsm").Activate
    Sheets("Sheet1").Select
    Range("B5:E19").Select
    Selection.Copy
    Windows("Workbook2.xlsm").Activate
    Sheets("Sheet1").Select
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("B5").Select
    End Sub

    =IF('Show Info'!L11="","",'Show Info'!L11) This formula is in the "Workbook1.xlsm' workbook When I copy it into the workbook2.xlsm it converts to

    =IF('[workbook1.xlsm]Show Info'!L11="","",'[workbook1.xlsm]Show Info'!L11)

    I only show one formula but you get the picture. I have many formulas to copy from one workbook to another.

  4. #4
    Board Regular
    Join Date
    Dec 2009
    Posts
    277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying a formula from one Excel worbook to another Excel workbook

    Sorry, I'm not certain I entirely understand the complexity of what you are trying to do, but I think this might handle the situation you have listed in your last post.

    It assumes that workbook2.xlsm has a "Show Info" sheet -- otherwise the formula will error out once moved.


    Code:
    Sub Schedule()
    
    
    Workbooks("Workbook2.xlsm").Worksheets("Sheet1").Range("b5:b19").Formula = Workbooks("Workbook1.xlsm").Worksheets("Sheet1").Range("b5:b19").Formula
    
    
    End Sub
    My favorite Excel Add-in:= Nutilities

  5. #5
    Board Regular
    Join Date
    Dec 2004
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying a formula from one Excel worbook to another Excel workbook

    Hi ODIN

    Thanks for the code. I will try it out tomorrow.

Some videos you may like

User Tag List

Tags for this Thread

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
  •