refering to cell data within a macro
 

Within my spreadsheet i have say cell A1 containing a name, lets say
fredbloggs.

I then have a macro which contains the line

Windows("fredbloggs.xls").activate

because there is also a sheet in that same name.

Instead of the macro line saying "fredbloggs.xls" is there any way of
putting the cell reference of 'A1' within the macro line ?

So in essence the window that will be opened will be the one named in
cell A1 on the spreadsheet ?

Your comments are greatly valued

Thank you

Darren


--
DarrenWood
------------------------------------------------------------------------
DarrenWood's Profile: http://www.excelforum.com/member.php...o&userid=16460
View this thread: http://www.excelforum.com/showthread...hreadid=383322

refering to cell data within a macro
 

try something like...

Windows(sheets("Sheet Name").Range("A1").Value & ".xls").activate

--
HTH...

Jim Thomlinson


"DarrenWood" wrote:


Within my spreadsheet i have say cell A1 containing a name, lets say
fredbloggs.

I then have a macro which contains the line

Windows("fredbloggs.xls").activate

because there is also a sheet in that same name.

Instead of the macro line saying "fredbloggs.xls" is there any way of
putting the cell reference of 'A1' within the macro line ?

So in essence the window that will be opened will be the one named in
cell A1 on the spreadsheet ?

Your comments are greatly valued

Thank you

Darren


--
DarrenWood
------------------------------------------------------------------------
DarrenWood's Profile: http://www.excelforum.com/member.php...o&userid=16460
View this thread: http://www.excelforum.com/showthread...hreadid=383322

refering to cell data within a macro
 

Thanks jim

I have tried your suggestion and typed

Windows(sheets("summary sheet").Range("A8").Value&".xls").activate

"summary sheet" being the sheet on which cell A8 is and
A8 being the cell which includes the name that i wish to use to open u
the other spreadsheet.

However when running it i get Compile error: syntax error

Have i misunderstood your suggestion?

Thank

--
DarrenWoo
-----------------------------------------------------------------------
DarrenWood's Profile: http://www.excelforum.com/member.php...fo&userid=1646
View this thread: http://www.excelforum.com/showthread.php?threadid=38332

refering to cell data within a macro
 

Try putting those spaces back:

Windows(Sheets("summary sheet").Range("A8").Value & ".xls").Activate



DarrenWood wrote:

Thanks jim

I have tried your suggestion and typed

Windows(sheets("summary sheet").Range("A8").Value&".xls").activate

"summary sheet" being the sheet on which cell A8 is and
A8 being the cell which includes the name that i wish to use to open up
the other spreadsheet.

However when running it i get Compile error: syntax error

Have i misunderstood your suggestion?

Thanks

--
DarrenWood
------------------------------------------------------------------------
DarrenWood's Profile: http://www.excelforum.com/member.php...o&userid=16460
View this thread: http://www.excelforum.com/showthread...hreadid=383322

--

Dave Peterson

refering to cell data within a macro
 

Ok Dave,

I have put the two spaces in and now get a different error messag
which says Run time error '9': subscript out of range.

Ill just run through again what i am doing in detail.

1 I am already in my excel file with my main summary sheet open ( name
"summary sheet" )

2 cell A1 on that same page has the name "fredbloggs" in

3 I also have a seperate excel file open but minimized in the name o
"fredbloggs" which has its first sheet named "activity sheet" and
second sheet named "summary sheet"

4) so whilst I am in my main summary sheet ( position1 above ) I wan
to be able to start the macro ( i do this by ctrl + D ) and for it the
to maximize the excel file in the name of "fredbloggs" because that i
the data in A1 on the main summary sheet and display the "summar
sheet" within that file.

I hope that might be clearer ?

--
DarrenWoo
-----------------------------------------------------------------------
DarrenWood's Profile: http://www.excelforum.com/member.php...fo&userid=1646
View this thread: http://www.excelforum.com/showthread.php?threadid=38332

refering to cell data within a macro
 

Subscript out of range means that something doesn't exist.

With your statement:
Windows(Sheets("summary sheet").Range("A8").Value & ".xls").Activate

It could be that the activeworkbook doesn't have a worksheet named "summary
sheet" (watch for extra spaces (leading/trailing or embedded).

Or the window named after the value in A8 of "summary sheet" (with .xls
appended) doesn't exist.

So check your worksheet's name. Then check A8 of that worksheet. Then look to
see if there is a window with that name (if you have multiple windows open to
that workbook, you could see book1.xls:1 or book1.xls:2--and they won't match a
window that's named book1.xls.)

Personally, I don't like going through the windows collection.

I'd use something like:

application.goto workbooks("fredbloggs.xls").worksheets("sheet1").r ange("a1"), _
scroll:=true

===
Or if I had to pick it up from a cell:

Application.Goto Workbooks(ActiveWorkbook.Worksheets("summary sheet") _
.Range("A8").Value & ".xls").Worksheets("sheet1").Range("a1"), _
scroll:=True

If that summary sheet isn't in the activeworkbook, maybe you could use
Thisworkbook--the workbook with the code?



DarrenWood wrote:

Ok Dave,

I have put the two spaces in and now get a different error message
which says Run time error '9': subscript out of range.

Ill just run through again what i am doing in detail.

1 I am already in my excel file with my main summary sheet open ( named
"summary sheet" )

2 cell A1 on that same page has the name "fredbloggs" in

3 I also have a seperate excel file open but minimized in the name of
"fredbloggs" which has its first sheet named "activity sheet" and a
second sheet named "summary sheet"

4) so whilst I am in my main summary sheet ( position1 above ) I want
to be able to start the macro ( i do this by ctrl + D ) and for it then
to maximize the excel file in the name of "fredbloggs" because that is
the data in A1 on the main summary sheet and display the "summary
sheet" within that file.

I hope that might be clearer ??

--
DarrenWood
------------------------------------------------------------------------
DarrenWood's Profile: http://www.excelforum.com/member.php...o&userid=16460
View this thread: http://www.excelforum.com/showthread...hreadid=383322

--

Dave Peterson

refering to cell data within a macro
 

Thanks Dave

You truly are a source of all knowledge.

I have used your suggestions instead of the 'windows' and they wor
perfectly.

Thanks again

Darre

--
DarrenWoo
-----------------------------------------------------------------------
DarrenWood's Profile: http://www.excelforum.com/member.php...fo&userid=1646
View this thread: http://www.excelforum.com/showthread.php?threadid=38332