CODEWITHSUNDEEP
excelvba
icalderon
icalderon

Reputation: 63

How can I insert a formula into another sheet referring to a cell in the Active Sheet?

I have a sheet called Line Item Summary with cell E7 that has the formula ='Costing Sheet'!I75. Instead of having the formula reference Costing Sheet I would like to replace it with the sheet that is being worked on (so the active sheet) .

The active sheet name may change as copies are made but the cell I75 where there data is referencing will always be the same.

I have tried the following code but I get "Run-time error '9':Subscript out of range".

Sub Connect()

Sheets("Line Item Summary").Range("E7").Formula = "='ActiveSheet'!I75"

End Sub

I tried to explain this as best I can but please let me know if it needs clarification. Thanks!

Upvotes: 0

Views: 47

Answers (1)

SJR
SJR

Reputation: 23081

Remove the activesheet from the quotes otherwise it is treated as a literal string.

Sub Connect()

Sheets("Line Item Summary").Range("E7").Formula = "='" & ActiveSheet.name & "'!I75"

End Sub

But your formula will change - is that what you intend?

Upvotes: 1

Related Questions