ExcelBanter - Sheets names

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Sheets names (https://www.excelbanter.com/excel-programming/377881-sheets-names.html)

Hi Guys,

I need a VBA to sort all the sheet's names in a column,
Can this list be updated automatically In case I delete any sheets?

Can anyone please direct me to create such VBA?

Thanks in advance.

HI

Sub listSheets()
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Regards
Peter

"2007-User" wrote:

Hi Guys,

I need a VBA to sort all the sheet's names in a column,
Can this list be updated automatically In case I delete any sheets?

Can anyone please direct me to create such VBA?

Thanks in advance.

Thank you so much Peter

How can I make it auto_refreshable in case I add/removed or renamed any
sheets?

Thanks again.

"PeterAtherton" wrote in message
...
HI

Sub listSheets()
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Regards
Peter

"2007-User" wrote:

Hi Guys,

I need a VBA to sort all the sheet's names in a column,
Can this list be updated automatically In case I delete any sheets?

Can anyone please direct me to create such VBA?

Thanks in advance.

Hi
Right-click on the Excel icon at the left of the file toolbar and choose
View Code. This opens the Workbook code sheet. Copy the revised formula into
the code.

Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
'sheets("Menu").select
'cells(4,2).select
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Beware the code write the sheets every time you save the workbook. in this
case I think you should insert lines to place the list on a particular sheet
and lines.

I've added a couple of lines to the code to give you the idea; just remove
the rems (apostrophes) if they are suitable

Regards
Peter
"2007-User" wrote:

Thank you so much Peter

How can I make it auto_refreshable in case I add/removed or renamed any
sheets?

Thanks again.

"PeterAtherton" wrote in message
...
HI

Sub listSheets()
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Regards
Peter

"2007-User" wrote:

Hi Guys,

I need a VBA to sort all the sheet's names in a column,
Can this list be updated automatically In case I delete any sheets?

Can anyone please direct me to create such VBA?

Thanks in advance.

I have not woken up yet. You might delete sheets so you will need to clear
the original list. The rest is OK - it still goes in the workbook module.

Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Sheets("Menu").Select
Range("B2").Select
' you might delete sheets so the updated
' list will be smaller
Range("B2").CurrentRegion.ClearContents
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

regards

"PeterAtherton" wrote:

Hi
Right-click on the Excel icon at the left of the file toolbar and choose
View Code. This opens the Workbook code sheet. Copy the revised formula into
the code.

Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
'sheets("Menu").select
'cells(4,2).select
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Beware the code write the sheets every time you save the workbook. in this
case I think you should insert lines to place the list on a particular sheet
and lines.

I've added a couple of lines to the code to give you the idea; just remove
the rems (apostrophes) if they are suitable

Regards
Peter
"2007-User" wrote:

Thank you so much Peter

How can I make it auto_refreshable in case I add/removed or renamed any
sheets?

Thanks again.

"PeterAtherton" wrote in message
...
HI

Sub listSheets()
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Regards
Peter

"2007-User" wrote:

Hi Guys,

I need a VBA to sort all the sheet's names in a column,
Can this list be updated automatically In case I delete any sheets?

Can anyone please direct me to create such VBA?

Thanks in advance.

Thanks so much Peter you help me a lot.

"PeterAtherton" wrote in message
...
I have not woken up yet. You might delete sheets so you will need to clear
the original list. The rest is OK - it still goes in the workbook module.

Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As
Boolean)
Sheets("Menu").Select
Range("B2").Select
' you might delete sheets so the updated
' list will be smaller
Range("B2").CurrentRegion.ClearContents
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

regards

"PeterAtherton" wrote:

Hi
Right-click on the Excel icon at the left of the file toolbar and choose
View Code. This opens the Workbook code sheet. Copy the revised formula
into
the code.

Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As
Boolean)
'sheets("Menu").select
'cells(4,2).select
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Beware the code write the sheets every time you save the workbook. in
this
case I think you should insert lines to place the list on a particular
sheet
and lines.

I've added a couple of lines to the code to give you the idea; just
remove
the rems (apostrophes) if they are suitable

Regards
Peter
"2007-User" wrote:

Thank you so much Peter

How can I make it auto_refreshable in case I add/removed or renamed any
sheets?

Thanks again.

"PeterAtherton" wrote in
message
...
HI

Sub listSheets()
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Regards
Peter

"2007-User" wrote:

Hi Guys,

I need a VBA to sort all the sheet's names in a column,
Can this list be updated automatically In case I delete any sheets?

Can anyone please direct me to create such VBA?

Thanks in advance.

Peter,

1- Can I call this list_refresher when I am switching between the sheets?
2- I have a little problem, when I am in other sheets, when I push the save
bottom the list create itself on that sheet also, is there anyway to lead
the program to run itself just on that specific sheet?

Thanks again.

"PeterAtherton" wrote in message
...
I have not woken up yet. You might delete sheets so you will need to clear
the original list. The rest is OK - it still goes in the workbook module.

Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As
Boolean)
Sheets("Menu").Select
Range("B2").Select
' you might delete sheets so the updated
' list will be smaller
Range("B2").CurrentRegion.ClearContents
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

regards

"PeterAtherton" wrote:

Hi
Right-click on the Excel icon at the left of the file toolbar and choose
View Code. This opens the Workbook code sheet. Copy the revised formula
into
the code.

Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As
Boolean)
'sheets("Menu").select
'cells(4,2).select
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Beware the code write the sheets every time you save the workbook. in
this
case I think you should insert lines to place the list on a particular
sheet
and lines.

I've added a couple of lines to the code to give you the idea; just
remove
the rems (apostrophes) if they are suitable

Regards
Peter
"2007-User" wrote:

Thank you so much Peter

How can I make it auto_refreshable in case I add/removed or renamed any
sheets?

Thanks again.

"PeterAtherton" wrote in
message
...
HI

Sub listSheets()
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Regards
Peter

"2007-User" wrote:

Hi Guys,

I need a VBA to sort all the sheet's names in a column,
Can this list be updated automatically In case I delete any sheets?

Can anyone please direct me to create such VBA?

Thanks in advance.

I found a solution to solve my 2nd problem which is using activesheet code,
please see below:

If ActiveSheet.Name = "COVER SHEET" Then
listSheets 'call listsheets module

"2007-User" wrote in message
...
Peter,

1- Can I call this list_refresher when I am switching between the sheets?
2- I have a little problem, when I am in other sheets, when I push the
save bottom the list create itself on that sheet also, is there anyway to
lead the program to run itself just on that specific sheet?

Thanks again.

"PeterAtherton" wrote in message
...
I have not woken up yet. You might delete sheets so you will need to clear
the original list. The rest is OK - it still goes in the workbook module.

Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As
Boolean)
Sheets("Menu").Select
Range("B2").Select
' you might delete sheets so the updated
' list will be smaller
Range("B2").CurrentRegion.ClearContents
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

regards

"PeterAtherton" wrote:

Hi
Right-click on the Excel icon at the left of the file toolbar and choose
View Code. This opens the Workbook code sheet. Copy the revised formula
into
the code.

Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As
Boolean)
'sheets("Menu").select
'cells(4,2).select
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Beware the code write the sheets every time you save the workbook. in
this
case I think you should insert lines to place the list on a particular
sheet
and lines.

I've added a couple of lines to the code to give you the idea; just
remove
the rems (apostrophes) if they are suitable

Regards
Peter
"2007-User" wrote:

Thank you so much Peter

How can I make it auto_refreshable in case I add/removed or renamed
any
sheets?

Thanks again.

"PeterAtherton" wrote in
message
...
HI

Sub listSheets()
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Regards
Peter

"2007-User" wrote:

Hi Guys,

I need a VBA to sort all the sheet's names in a column,
Can this list be updated automatically In case I delete any sheets?

Can anyone please direct me to create such VBA?

Thanks in advance.