Use variable across workbooks

[Steph]

Hello. I have a workbook with a click event. The click event opens another
workbook and runs a procedure in the newly opened workbook.

I would like to read the contents of of Sheet1, cell A1 in the file that
houses the click event, and be able to use that variable in the code in the
workbook that the click event opened. Can this be done?? Thanks.

 

[Guest]

Yes you can; you'll get the idea from this:

workbooks("Book1").Sheets("Sheet1").Range("A2").value
=workbooks("Book2").Sheets("Sheet1").Range("A1").value
or
myVar=workbooks("Book1").Sheets("Sheet1").Range("A2").value
or
MyFunction(workbooks("Book1").Sheets("Sheet1").Range("A2").value )

This may not be robust as it assumes the names of the workbooks and sheets
etc etc.
What you cannot do so easily is cross-reference a variable in the VBA code
of one workbook in another.

 

[Steph]

That's part of the problem - I won't know the name of the file or sheet name
of the workbook that houses the click event whic then opens and runs the
code in the newly opened workbook. So somehow I need the contents of cell
A1 to carry over and be available to me in the newly oppened workbook.