Cell referencing AciveSheet when pasting formula into another workbook

[r2fro]

Hello there,
I'm having trouble cell referencing my active workbook in a formula I wish to paste into another workbook.

I think it may be a syntax error but unfortunately I cannot spot problem. (currently only the formula appears in the destination cell but not the data.



It's just a simple formula combining txt from two cells with the &", "& separator .



My code looks like this so far:

Sub Copy_Data_Ledger()
'
' Copy_Data_Ledger Macro
'

Application.ScreenUpdating = False
    Dim mySourceWB As Workbook
    Dim mySourceSheet As Worksheet
    Dim myDestWB As Workbook
    Dim myDestSheet As Worksheet

'   First capture current workbook and worksheet
    Set mySourceWB = ActiveWorkbook
    Set mSourceSheet = ActiveSheet


    Windows("Balance Sheet.xlsm").Activate
    Sheets("Ledger").Select
    Range("AL8").Select
   ActiveCell.Formula = "='(['" & mySourceWB.Name & "']'" & mSourceSheet.Name & "'!$E$5) &" "& (['" & mySourceWB.Name & "']'" & mySourceSheet.Name & "'!$C$5)"

Hope you can help

 

[Norie]

You don't have any workbook/worksheet references for A8, so the formula will go in A8 of whatever is the currently active sheet.

Try this.

Workbooks("Balance Sheet.xlsm").Sheets("Ledger").Range("AL8").Formula = "='(['" & mySourceWB.Name & "']'" & mSourceSheet.Name & "'!$E$5) &" "& (['" & mySourceWB.Name & "']'" & mySourceSheet.Name & "'!$C$5)"

 

[mikerickson]

Looking at the posted code, I'd suggest

With mySourceSheet
    ActiveCell.Formula = "=" & .Range("E5").Address(,,,True) & " " & .Range("C5").Address(,,,True)
End With

But that would return a #NULL error, since the intersection of two different single cells is problematic.

What formula are you trying to put in the cell?

 

[r2fro]

Looking at the posted code, I'd suggest

With mySourceSheet
    ActiveCell.Formula = "=" & .Range("E5").Address(,,,True) & " " & .Range("C5").Address(,,,True)
End With

But that would return a #NULL error, since the intersection of two different single cells is problematic.

What formula are you trying to put in the cell?

The formula is a simply combining two text cells.
Let’s say the active sheet is called “Steart Street, 4.xlsm” then:

[FONT=Calibri][SIZE=3][COLOR=#000000]AL8 = '[Stewart Street, 4.xlsm]Sheet1'!$E$5&", "&'[Stewart Street, 4.xlsm]Sheet1'!$C$5[/COLOR][/SIZE][/FONT]

 

[Norie]

If the formula is correct the code I posted should work, it'll put the formula in A8 of sheet 'Ledger' in workbook 'Balance Sheet.xlsm'.

 

[mikerickson]

Try

With mySourceSheet
    ActiveCell.Formula = "=" & .Range("E5").Address(,,,True) & "&" & .Range("C5").Address(,,,True)
End With