refer to cell in another workbook with variable sheet name

**wonderdunder** · 06-30-2011, 10:15 AM

Can anyone provide VBA for the following:

I have two workbooks (Book1 and Book2). The code operates from Book 1.

In Book1, Sheet1, Cell A1 is some text (A 3 letter code) which changes according to other cirteria.

Book 2 contains several sheets which are named with the text I just mentioned followed by another word ('Result'). Cell B3 in each contains a number that I want to refer to.I need VBA code that will refer to the relevant sheet using the text in A1 but I can't seem to get anything working.

I've tried the following which I suppose shows my complete lack of understanding of VBA...:

Please Login or Register  to view this content.

**Mordred** · 06-30-2011, 10:53 AM

Hi wonderdunder, the following code has not been tested at run-time but give it a try.

Please Login or Register  to view this content.

Also, I don't know what you were trying to achieve with "Cert No" from your post. At any rate, good luck!

Regards

**Mordred** · 06-30-2011, 12:40 PM

Hi wonderdunder, my last post does not work. However, try

Please Login or Register  to view this content.

**wonderdunder** · 06-30-2011, 02:15 PM

Many thanks - Haven' tried it yet but I suspected all these Dim things would crop up! Just seems an awful lot of code for such a simple task. But hey.....

I'll let you know how it goes.