Cell referencing AciveSheet when pasting formula into another workbook

r2fro

New Member
Joined
Dec 4, 2013
Messages
11
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:

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,364
Office Version
365
Platform
Windows
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.
Code:
Workbooks("Balance Sheet.xlsm").Sheets("Ledger").Range("AL8").Formula = "='(['" & mySourceWB.Name & "']'" & mSourceSheet.Name & "'!$E$5) &" "& (['" & mySourceWB.Name & "']'" & mySourceSheet.Name & "'!$C$5)"
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,814
Looking at the posted code, I'd suggest
Code:
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

New Member
Joined
Dec 4, 2013
Messages
11
Looking at the posted code, I'd suggest
Rich (BB code):
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:
Code:
[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

Well-known Member
Joined
Apr 28, 2004
Messages
75,364
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,814
Try
Rich (BB code):
With mySourceSheet
    ActiveCell.Formula = "=" & .Range("E5").Address(,,,True) & "&" & .Range("C5").Address(,,,True)
End With
 

Forum statistics

Threads
1,082,557
Messages
5,366,304
Members
400,882
Latest member
zaldy_a

Some videos you may like

This Week's Hot Topics

Top