Reference Workbook and sheet from a cell using VBA

[Xrull]

Good night (or day depending where you are in the world)
Is it possible for the VBA code to capture the open workbook name and sheet name from a cell?
I've been trying to tweak this code:

Please Login or Register  to view this content.

I tried using this line of code:

Please Login or Register  to view this content.

but no matter how I try, it doesn't work.
The code pulls from WorksheetName2 when it is open. If there is a way to pull it when the workbook is closed, I'd be grateful.
Thanks for your help,
Xrull

[ravishankar]

HI
you need to add filepath.
if filepath = "C:\imports\coffee\" then

Please Login or Register  to view this content.

will return the value of J11 in cell A1 of current book.
Ravi

[Xrull]

Ravishankar,
I'm having a little problem getting your code to work.

Please Login or Register  to view this content.

I'm still working on it.
Thanks,
Xrull

[ravishankar]

Hi
try

Please Login or Register  to view this content.

Ravi

[Xrull]

Ravishankar,
I've attached 2 files to explain what I'm trying to achieve.

If the 2 books are open and you press the "Copy Info" button the information from Worksheet2 range A:C will be pulled by the code:

Please Login or Register  to view this content.

However, sometimes I need 2 pages, and need it to accumulate.
I want to be able to type the name of any sheet name of Workbook2 and pull the range A1 to the last cell in Column C. I don't mind doing this with the Workbook2 open. If I can pull the information when the sheet is selected, that would be good.
I tried this code:

Please Login or Register  to view this content.

It failed.
Thank you.

[ravishankar]

Hi
try these codes.

Please Login or Register  to view this content.

Ravi

[Xrull]

ravishankar,
The macro is just what I needed.
Thank you very much,
Xrull