Seach for all worksheet and copy the name

Solved/Closed
cy - Sep 22, 2011 at 03:13 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 23, 2011 at 06:50 PM
Hello,
I'm trying to see if anyone can help me with this, I do have a master excel file that get info from other excel files, currently I have to do manually add worksheet on the other file to the master file, is there any way I can either write a Macro or any other way that I can search all Sheets on one file and copy the name of Sheets to Master file? Thanks in advance..


2 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Sep 23, 2011 at 03:41 AM
Just copy the name ?
0
Ummm..copy the name I was hoping to get a better answer out of this! here is what I got/want:


file-A.xls has 20 worksheets(say ws-red, ws-blue, and so on)
file-B.xls has 5 worksheets(say ws-yellow, broan, black, and so on)
From Master file say Master_db.xls I want to copy all then worksheets name from file -A and file-B to my Maste_db.xls file, here is what I have

I know I can get the name from Sheets.Count as long as it is in the same file but in my case I want to get them from other workbook or file!

For i = 1 To Sheets.Count
Cells(x, 1) = Sheets(i).Name
Next i
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Sep 23, 2011 at 02:48 PM
That was the question not the answer :P

This is what i think you want to do (not tested)

Set wbOther = Workbooks("c:\myBook.xls")

For i = 1 To wbOther.Sheets.Count
Cells(x, 1) = wbOther.Sheets(i).Name
Next i

set wbOther =nothing
0
Thanks got it to work, by the way while we are in the same topic, now I got all the name, what if I want to do the following:

What if I want to use a formula such wbOther.Sheets(i).Name !$A$A1 in above code in to the one of the cell, say on master file after all worksheet name is copied say into the cell B5, I want to refenece that name to the other cell with a function or formula suchj as ='nameofworksheet' !$A$A11...do you know how to do this!

Thanks again in advance..
Cheers
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Sep 23, 2011 at 04:55 PM
you would need to use INDIRECT function with or without addtional call to ADDRESS function
So either it would be

=INDIRECT(<your string comes here>)
or

=INDIRECT(ADDRESS(<address parameter here>))
0
Rizvisa, thanks but it does not work when I use =INDIRECT(B5,$A$A11)
Where B5 is the cell which has the value I got from code above say apple
$A$A11 is an absolute value I need from other worksheet!
0
Thanks...I put it into the code and works now..
Thanks again..
0