In a macro I have a string variable (macroName) that is set to the name of a
workbook. In the macro I am trying to retrieve information from the second
worksheet of that workbook while another workbook is active.

I have tried variations of the following syntax, but can't get either
statement below to work.

ColumnID = Workbooks(MacroName).Sheets(2).Cells(1, _ NumColsMacro).Address
NormalHomeRng = Workbooks(MacroName).Sheets(2) & "!$A$1"

Could someone please give me the correct syntax for these two lines?

TIA.

Not sure on the first one, as I don't knowm what _NumColsMacro is and why
you get the cell address, but it seems okay.

For the second, try

Set NormalHomeRng = Workbooks(MacroName).Sheets(2).Range("$A$1")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ken" wrote in message
...
In a macro I have a string variable (macroName) that is set to the name of
a
workbook. In the macro I am trying to retrieve information from the second
worksheet of that workbook while another workbook is active.

I have tried variations of the following syntax, but can't get either
statement below to work.

ColumnID = Workbooks(MacroName).Sheets(2).Cells(1, _ NumColsMacro).Address
NormalHomeRng = Workbooks(MacroName).Sheets(2) & "!$A$1"

Could someone please give me the correct syntax for these two lines?

TIA.