Seach for all worksheet and copy the name
Solved/Closed
cy
-
Sep 22, 2011 at 03:13 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 23, 2011 at 06:50 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 23, 2011 at 06:50 PM
Related:
- Seach for all worksheet and copy the name
- Transfer data from one excel worksheet to another automatically - Guide
- How do you add a username or author to a worksheet - Guide
- Automatically run a macro when opening a worksheet - Guide
- Transfer data from one excel worksheet to another automatically based on criteria ✓ - Excel Forum
- Excel vba copy rows to another worksheet based on criteria ✓ - Excel Forum
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Sep 23, 2011 at 03:41 AM
Sep 23, 2011 at 03:41 AM
Just copy the name ?
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Sep 23, 2011 at 02:48 PM
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
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
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Sep 23, 2011 at 04:55 PM
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>))
So either it would be
=INDIRECT(<your string comes here>)
or
=INDIRECT(ADDRESS(<address parameter here>))
