CODEWITHSUNDEEP
excel
Coding Novice
Coding Novice

Reputation: 447

Force Excel Formula to Reference Current Workbook

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

Upvotes: 1

Views: 3058

Answers (2)

RayInNoIL
RayInNoIL

Reputation: 907

The trick is to use INDIRECT(). For example:

=INDIRECT("Form!B6")

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 1

Related Questions