Get Worksheet Name
 

I'm a newbie so please forgive me if this topic has been posted. I
conducted a search, but didn't find a related topic.

I have created a way of tracking my time and pay for multiple jobs
(don't ask). In a nut shell, I use a seperate excel file (workbook) for
each month. Then I have a seperate worksheet within each file that
pretains to work done in a particular week. The names of the worksheets
are based on the the Sunday date of that week, ex. the worksheet that
pretains to January 8, 2006 thus is named 01082006. In addition, each
file has a worksheet that sums everything that happened in the weeks of
that month.

Therefore, FEB06.xls ( the file for February '06) has six worksheets.
They are named Total, 02012006, 02052006, 02122006, 02192006, 02262006.
I would like to write a function that would allow me to call
(Get/Insert/Whatever???) the name of one of the worksheets into a cell,
then a function that would allow me to call a different worksheet name
into a different cell.

As a result, the function in A14 would return 02012006, A15 would
return 02052006, etc....

Any help would be greatly appreciated.

Thanks

Omni

Get Worksheet Name
 

try this

Option Explicit
Sub WsNames()
Dim i As Long
Dim sh As Worksheet
i = 14

For Each sh In ActiveWorkbook.Sheets
Range("A" & i) = sh.Name
i = i + 1
Next

End Sub

--


Gary


"Omni_belk" wrote in message
oups.com...
I'm a newbie so please forgive me if this topic has been posted. I
conducted a search, but didn't find a related topic.

I have created a way of tracking my time and pay for multiple jobs
(don't ask). In a nut shell, I use a seperate excel file (workbook) for
each month. Then I have a seperate worksheet within each file that
pretains to work done in a particular week. The names of the worksheets
are based on the the Sunday date of that week, ex. the worksheet that
pretains to January 8, 2006 thus is named 01082006. In addition, each
file has a worksheet that sums everything that happened in the weeks of
that month.

Therefore, FEB06.xls ( the file for February '06) has six worksheets.
They are named Total, 02012006, 02052006, 02122006, 02192006, 02262006.
I would like to write a function that would allow me to call
(Get/Insert/Whatever???) the name of one of the worksheets into a cell,
then a function that would allow me to call a different worksheet name
into a different cell.

As a result, the function in A14 would return 02012006, A15 would
return 02052006, etc....

Any help would be greatly appreciated.

Thanks

Omni

Get Worksheet Name
 

Thanks Gary. However, I am such a newbie, I have no idea were to paste
this. Thanks for the reply.

Get Worksheet Name
 

you can either paste it on the sheet code page or a module.

1. right click the sheet you're using and click view code

or

2. press alt-f11
if the project explorer is not visible on the left, click view and then
project explorer

right click on one of the sheets and choose insert module
paste the code there

then alt-f8 and run the macro



--


Gary


"Omni_belk" wrote in message
ups.com...
Thanks Gary. However, I am such a newbie, I have no idea were to paste
this. Thanks for the reply.

Get Worksheet Name
 

For a non-programming method, see

http://www.mcgimpsey.com/excel/formu..._function.html



In article .com,
"Omni_belk" wrote:

I'm a newbie so please forgive me if this topic has been posted. I
conducted a search, but didn't find a related topic.

I have created a way of tracking my time and pay for multiple jobs
(don't ask). In a nut shell, I use a seperate excel file (workbook) for
each month. Then I have a seperate worksheet within each file that
pretains to work done in a particular week. The names of the worksheets
are based on the the Sunday date of that week, ex. the worksheet that
pretains to January 8, 2006 thus is named 01082006. In addition, each
file has a worksheet that sums everything that happened in the weeks of
that month.

Therefore, FEB06.xls ( the file for February '06) has six worksheets.
They are named Total, 02012006, 02052006, 02122006, 02192006, 02262006.
I would like to write a function that would allow me to call
(Get/Insert/Whatever???) the name of one of the worksheets into a cell,
then a function that would allow me to call a different worksheet name
into a different cell.

As a result, the function in A14 would return 02012006, A15 would
return 02052006, etc....

Any help would be greatly appreciated.

Thanks

Omni

Get Worksheet Name
 

Thanks
That is what I needed.

Get Worksheet Name
 

Gary-

Thanks, however I made an error in my example.

Instead of going down (A14, A15, A16 etc... I need to go sideways (A3,
B3, C3....)

I tried to edit the formula, but that didn't work.

I tried:
Option Explicit
Sub WsNames()
Dim i As Long
Dim sh As Worksheet
i = G

For Each sh In ActiveWorkbook.Sheets
Range("1" & i) = sh.Name
i = i + 1
Next

End Sub


Obviously this doesn't work, and I don't know enough to be able to
correct this. Can you help me again?

Thanks

Omni

Get Worksheet Name
 

try this

Option Explicit
Sub WsNames()
Dim i As Long
Dim sh As Worksheet
i = 1

For Each sh In ActiveWorkbook.Sheets
Cells(3, i) = sh.Name
i = i + 1
Next

End Sub



--


Gary


"Omni_belk" wrote in message
ups.com...
Gary-

Thanks, however I made an error in my example.

Instead of going down (A14, A15, A16 etc... I need to go sideways (A3,
B3, C3....)

I tried to edit the formula, but that didn't work.

I tried:
Option Explicit
Sub WsNames()
Dim i As Long
Dim sh As Worksheet
i = G

For Each sh In ActiveWorkbook.Sheets
Range("1" & i) = sh.Name
i = i + 1
Next

End Sub


Obviously this doesn't work, and I don't know enough to be able to
correct this. Can you help me again?

Thanks

Omni

Get Worksheet Name
 

Gary,

Thanks Gary. The code you gave me works just as I asked. However, my
question was flawed as I don't need to list the name of sheet1. So is
it possible to have the module start with the name of sheet2? Sheet1
is a summary of the other sheets. As a result, I do not need to have
the name in sheet1 anywhere in sheet1. The data in column B will come
from sheet2, column C from sheet3, etc...

Thanks again for your help.

Omni

Get Worksheet Name
 

give this a try

Sub WsNames3()
Dim i As Long
Dim sh As Worksheet

For i = 2 To Worksheets.Count
Cells(3, i) = Worksheets(i).Name

Next

End Sub


--


Gary


"Omni_belk" wrote in message
ups.com...
Gary,

Thanks Gary. The code you gave me works just as I asked. However, my
question was flawed as I don't need to list the name of sheet1. So is
it possible to have the module start with the name of sheet2? Sheet1
is a summary of the other sheets. As a result, I do not need to have
the name in sheet1 anywhere in sheet1. The data in column B will come
from sheet2, column C from sheet3, etc...

Thanks again for your help.

Omni

Get Worksheet Name
 

don't need this line in the latest macro

Dim sh As Worksheet

one other thing, if you want to ensure the names always appear on sheet1,
use this modification

Sub WsNames3()
Dim i As Long

For i = 2 To Worksheets.Count
Worksheets("sheet1").Cells(3, i) = Worksheets(i).Name
Next

End Sub


--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
give this a try

Sub WsNames3()
Dim i As Long
Dim sh As Worksheet

For i = 2 To Worksheets.Count
Cells(3, i) = Worksheets(i).Name

Next

End Sub


--


Gary


"Omni_belk" wrote in message
ups.com...
Gary,

Thanks Gary. The code you gave me works just as I asked. However, my
question was flawed as I don't need to list the name of sheet1. So is
it possible to have the module start with the name of sheet2? Sheet1
is a summary of the other sheets. As a result, I do not need to have
the name in sheet1 anywhere in sheet1. The data in column B will come
from sheet2, column C from sheet3, etc...

Thanks again for your help.

Omni

Get Worksheet Name
 

Gary Keramidas wrote:
give this a try

Sub WsNames3()
Dim i As Long
Dim sh As Worksheet

For i = 2 To Worksheets.Count
Cells(3, i) = Worksheets(i).Name

Next

End Sub


--


Gary

Thanks Gary. That's Perfect.

Omni