Force Excel Formula to Reference Current Workbook

Question

I have a simple formula reference that I use in my workbook, however it gets complicated when I use another function that instantly opens my default worksheet and copies it over to my active workbook.

The problem is that the cells in this workbook reference another sheet in my default workbook. The sheet in that and all the other workbooks I am working on has the same name. It's "Form"

When I use my code to copy the sheet over, the cell automatically changes it's reference to include the previous workbook.

I want the formula to ALWAYS USE THE CURRENT WORKBOOK.

Here is what I use

=Form!B6

Here is what I end up getting when i drop the sheet

="filepath"Form!B6

You can do a Replace All by replacing "filepath" to blank. — ian0411
You can append all the cells with ' at the begining, copy/paste then remove the ' — Scott Craner
Scott, funny you mention that. That's my temporary solution actually. I'm still looking for a robust solution. Ian same thing. I'm looking for a no step solution. — Coding Novice

Gary's Student Gary's Student · Accepted Answer · 2017-10-12T19:06:22

Here is a way to copy a formula from one workbook to another with no changes:

Sub ytrewq()
    Dim s As String

    s = Workbooks("book2.xlsm").Sheets("Sheet1").Range("G8").Formula
    Workbooks("temp.xlsm").Sheets("Sheet1").Range("H1").Formula = s
End Sub

Force Excel Formula to Reference Current Workbook

2 Answers