Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Getting & Using Sheet Names or Index in VBA code
How do you get each Sheet Name in a Workbook so you can use it in code? I
know that each Sheet, regardless of Name has an Index of 1 through n beginning with the leftmost Sheet but I don't know how to use that information. I have one reference book, Excel 2003 VBA, that says there is a Worksheet Property 'Name' that returns a String and in the Description of that Property it says 'Set/Get the name of the worksheet'. Unfortunately, it doesn't tell me how to use that in code. I don't really care whether I use the name of each Sheet or the Index but if you can use either it would be nice to know both ways. I know a way to write the code if I know the Sheet names but I want the code to be more flexible so I don't have to 'hard code' them. Thanks rw |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Getting & Using Sheet Names or Index in VBA code
'The MsgBox in the following code displays
'each Sheet name in the workbook. Sub SheetNames() Dim strSheetName As String Dim i As Long For i = 1 To Sheets.Count strSheetName = Sheets(i).Name MsgBox strSheetName Next i End Sub 'Alternative method Sub SheetNames_2() Dim ws As Worksheet Dim strSheetName As String For Each ws In Worksheets strSheetName = ws.Name MsgBox strSheetName Next ws End Sub -- Regards, OssieMac "rwjack" wrote: How do you get each Sheet Name in a Workbook so you can use it in code? I know that each Sheet, regardless of Name has an Index of 1 through n beginning with the leftmost Sheet but I don't know how to use that information. I have one reference book, Excel 2003 VBA, that says there is a Worksheet Property 'Name' that returns a String and in the Description of that Property it says 'Set/Get the name of the worksheet'. Unfortunately, it doesn't tell me how to use that in code. I don't really care whether I use the name of each Sheet or the Index but if you can use either it would be nice to know both ways. I know a way to write the code if I know the Sheet names but I want the code to be more flexible so I don't have to 'hard code' them. Thanks rw |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Getting & Using Sheet Names or Index in VBA code
I tested your examples and they worked great! With a little additional code
I think this will give me the results I want. Thanks... Regards, rw "OssieMac" wrote: 'The MsgBox in the following code displays 'each Sheet name in the workbook. Sub SheetNames() Dim strSheetName As String Dim i As Long For i = 1 To Sheets.Count strSheetName = Sheets(i).Name MsgBox strSheetName Next i End Sub 'Alternative method Sub SheetNames_2() Dim ws As Worksheet Dim strSheetName As String For Each ws In Worksheets strSheetName = ws.Name MsgBox strSheetName Next ws End Sub -- Regards, OssieMac "rwjack" wrote: How do you get each Sheet Name in a Workbook so you can use it in code? I know that each Sheet, regardless of Name has an Index of 1 through n beginning with the leftmost Sheet but I don't know how to use that information. I have one reference book, Excel 2003 VBA, that says there is a Worksheet Property 'Name' that returns a String and in the Description of that Property it says 'Set/Get the name of the worksheet'. Unfortunately, it doesn't tell me how to use that in code. I don't really care whether I use the name of each Sheet or the Index but if you can use either it would be nice to know both ways. I know a way to write the code if I know the Sheet names but I want the code to be more flexible so I don't have to 'hard code' them. Thanks rw |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Getting & Using Sheet Names or Index in VBA code
This macro will give you a list of sheetnames and their codenames.
Sub CreateListOfSheetsOnFirstSheet() Dim ws As Worksheet For i = 1 To Worksheets.Count With Worksheets(1) Set ws = Worksheets(i) .Cells(i, 1).Value = ws.Name .Cells(i, 2).Value = ws.CodeName End With Next i End Sub Gord Dibben MS Excel MVP On Thu, 10 Apr 2008 14:14:02 -0700, rwjack wrote: How do you get each Sheet Name in a Workbook so you can use it in code? I know that each Sheet, regardless of Name has an Index of 1 through n beginning with the leftmost Sheet but I don't know how to use that information. I have one reference book, Excel 2003 VBA, that says there is a Worksheet Property 'Name' that returns a String and in the Description of that Property it says 'Set/Get the name of the worksheet'. Unfortunately, it doesn't tell me how to use that in code. I don't really care whether I use the name of each Sheet or the Index but if you can use either it would be nice to know both ways. I know a way to write the code if I know the Sheet names but I want the code to be more flexible so I don't have to 'hard code' them. Thanks rw |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Getting & Using Sheet Names or Index in VBA code
Gord, thanks for this code as another way to do what I was looking for. This
works well too. I did have to declare 'i' because I use Option Explicit most of the time but it worked fine. Thanks for your response. Regards rw "Gord Dibben" wrote: This macro will give you a list of sheetnames and their codenames. Sub CreateListOfSheetsOnFirstSheet() Dim ws As Worksheet For i = 1 To Worksheets.Count With Worksheets(1) Set ws = Worksheets(i) .Cells(i, 1).Value = ws.Name .Cells(i, 2).Value = ws.CodeName End With Next i End Sub Gord Dibben MS Excel MVP On Thu, 10 Apr 2008 14:14:02 -0700, rwjack wrote: How do you get each Sheet Name in a Workbook so you can use it in code? I know that each Sheet, regardless of Name has an Index of 1 through n beginning with the leftmost Sheet but I don't know how to use that information. I have one reference book, Excel 2003 VBA, that says there is a Worksheet Property 'Name' that returns a String and in the Description of that Property it says 'Set/Get the name of the worksheet'. Unfortunately, it doesn't tell me how to use that in code. I don't really care whether I use the name of each Sheet or the Index but if you can use either it would be nice to know both ways. I know a way to write the code if I know the Sheet names but I want the code to be more flexible so I don't have to 'hard code' them. Thanks rw |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell names = sheet names | Excel Worksheet Functions | |||
Sheet names code | Excel Discussion (Misc queries) | |||
VB code to copy sheet format to another sheet | Excel Discussion (Misc queries) | |||
Creating and Index with worksheet names | Excel Discussion (Misc queries) | |||
I want to print out the sheet tabs (sheet names) | Excel Worksheet Functions |