Copying a formula from one Excel worbook to another Excel workbook

[pincivma]

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

 

[=ODIN=]

Can you post the code you are using, and what the formula in workbook1 reads?

 

[pincivma]

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.

 

[=ODIN=]

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.

Sub Schedule()


Workbooks("Workbook2.xlsm").Worksheets("Sheet1").Range("b5:b19").Formula = Workbooks("Workbook1.xlsm").Worksheets("Sheet1").Range("b5:b19").Formula


End Sub

 

[pincivma]

Hi ODIN

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