Work with ActiveCell from one worksheet in another

[JulieS]

I have two excel spreadsheets - one with the macro, and the others that I want the macro to run on.

I have successfully opened the other spreadsheet, activated it, and can read values from the cells, but when I reference ActiveCell, I get the following error:

Run-time error '1004':
Application-defined or object-defined error

The line this breaks down on is:

variable = activeCell.offset (value1, value2)

My code previous to this line is:

set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("SpreadsheetLocation.xls")
Set xlsheet = xlbook.sheets("Sheet1")
xlsheet.Activate
xlsheet.Range("E2").Activate

I want E2 to be the ActiveCell.

My question is, how do I reference the active cell in Spreadsheet A from a macro in spreadsheet B?

Any help would be greatly appreciated.

Thanks.

 

[SkipVought]

Hi,

I would advise against using the Select and Activate methods as process coding methods.

"My question is, how do I reference the active cell in Spreadsheet A from a macro in spreadsheet B?"

'you do not need a separate reference to a new instance of the Excel Application Object!!!

Set xlBook = Workbooks.Open("SpreadsheetLocation.xls")
Set xlsheet = xlbook.sheets("Sheet1")

'the ThisWorkbook Object refers to the workbook containing the macro code...

'now what do you want to do with xlsheet.[E2] cuz you don't need to activate.

faq707-4105

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]