refering to cell data within a macro

[DarrenWood]

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

[Jim Thomlinson]

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
>
>

[DarrenWood]

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

[Dave Peterson]

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

[DarrenWood]

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 ??

[Dave Peterson]

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").range("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

[DarrenWood]

Thanks Dave

You truly are a source of all knowledge.

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

Thanks again

Darren