Help with named-cell referencing
 

I have two open workbooks:

SPX-BS.xlsm and

Book1.xlsm

There is a cell named "premium" in SPX-BS which I need to access from a macro to post the value to Book1. If I enter manually

'SPX-BS.xlsm'!premium into a cell on Book1 it works fine. However if, within a macro I type

Range("A7") = 'SPX-BS.xlsm'!premium

I get an "Compile Error Syntax Error" message.

Can someone please advise what I'm doing wrong? Many thanks in advance.

Help with named-cell referencing
 

Hi,

Am Tue, 24 Nov 2020 11:54:10 -0800 (PST) schrieb programmernovice:

I have two open workbooks:

SPX-BS.xlsm and

Book1.xlsm

There is a cell named "premium" in SPX-BS which I need to access from a macro to post the value to Book1. If I enter manually

'SPX-BS.xlsm'!premium into a cell on Book1 it works fine. However if, within a macro I type

Range("A7") = 'SPX-BS.xlsm'!premium

I get an "Compile Error Syntax Error" message.

try:
Range("A7") = Workbooks("SPX-BS.xlsm").Sheets("Sheet1").Range("premium")

Modify the sheet name as expected.


Regards
Claus B.
--
Windows10
Office 2016

Help with named-cell referencing
 

On Tuesday, November 24, 2020 at 2:20:34 PM UTC-6, Claus Busch wrote:
Hi,
Am Tue, 24 Nov 2020 11:54:10 -0800 (PST) schrieb programmernovice:

I have two open workbooks:

SPX-BS.xlsm and

Book1.xlsm

There is a cell named "premium" in SPX-BS which I need to access from a macro to post the value to Book1. If I enter manually

'SPX-BS.xlsm'!premium into a cell on Book1 it works fine. However if, within a macro I type

Range("A7") = 'SPX-BS.xlsm'!premium

I get an "Compile Error Syntax Error" message.
try:
Range("A7") = Workbooks("SPX-BS.xlsm").Sheets("Sheet1").Range("premium")

Modify the sheet name as expected.


Regards
Claus B.

Many thanks for taking time to help me out, Claus