I have these 2 scripts. The first is for creating a sort of index
sheet of all the tabs in a workbook. It works extremely well but
there must be something that I'm not understanding in the macro since
any coding I add to format the resulting index sheet doesn't do
anything. I've tried placing the formatting code at the end and at
the beginning of the macro itself but the cell width, etc., isn't
affected. Yet when I run them separately, they both work
independently of each other.

Here are the two scripts:
-----------------------------------------------------------------
Sub SHEET_NAMES_a_list_all_with_NUMBERING()
'list of sheet names starting at B1
Dim Rng As Range
Dim Sheet As Worksheet
Dim i As Long

Worksheets.Add(befo=Worksheets(1)).Name = "ListOfSheetNames"
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name < "ListOfSheetNames" Then
Rng.Offset(i, 1).Value = Sheet.Name
Rng.Offset(i, 0).Value = i + 1
i = i + 1
End If
Next
End Sub
-----------------------------------------------------------------
Sub ColumnWidth_and_AutoFit_Set()
Columns("A:A").ColumnWidth = 10
Columns("B:B").ColumnWidth = 20

Cells.Select
Selection.Rows.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
Range("A1").Select
End Sub
-----------------------------------------------------------------

Lastly, a question. I like how simple the column width code above
is. I found that by the usual searching in the archives via google.
When I record the process I get the same but with selecting which is
never as safe, I believe, as having the straight code like Columns
(...).ColumnWidth ...

Is there a way to get the horizontal and vertical central alignment
and rows.AutoFit without selecting the cells as well, by any chance?

Thank you. :oD

It can never be bad to qualify your ranges/objects:

Sub SHEET_NAMES_a_list_all_with_NUMBERING()
'list of sheet names starting at B1
Dim Rng As Range
Dim sh as object 'Sheet As Worksheet, not all sheets are worksheets
Dim i As Long
Dim NewSheet as worksheet

set newsheet = Worksheets.Add(befo=sheets(1))
newsheet.Name = "ListOfSheetNames"
Set Rng = newsheet.Range("A1")
For Each sh In ActiveWorkbook.Sheets
If Sh.Name < "ListOfSheetNames" Then
Rng.Offset(i, 1).Value = Sh.Name
Rng.Offset(i, 0).Value = i + 1
i = i + 1
End If
Next sh
End Sub
-----------------------------------------------------------------
Sub ColumnWidth_and_AutoFit_Set()

dim wks as worksheet

set wks = activesheet 'then you'll get the VBE's intellisense

with wks
.Columns("A:A").ColumnWidth = 10
'or to be different
.range("B1").entirecolumn.ColumnWidth = 20

.rows.AutoFit
'or even
.usedrange.rows.autofit

with .cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With

'remember, you can only select a cell on the activesheet
'not a problem in this example.
.Range("A1").Select

end with
End Sub

StargateFanNotAtHome wrote:

I have these 2 scripts. The first is for creating a sort of index
sheet of all the tabs in a workbook. It works extremely well but
there must be something that I'm not understanding in the macro since
any coding I add to format the resulting index sheet doesn't do
anything. I've tried placing the formatting code at the end and at
the beginning of the macro itself but the cell width, etc., isn't
affected. Yet when I run them separately, they both work
independently of each other.

Here are the two scripts:
-----------------------------------------------------------------
Sub SHEET_NAMES_a_list_all_with_NUMBERING()
'list of sheet names starting at B1
Dim Rng As Range
Dim Sheet As Worksheet
Dim i As Long

Worksheets.Add(befo=Worksheets(1)).Name = "ListOfSheetNames"
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name < "ListOfSheetNames" Then
Rng.Offset(i, 1).Value = Sheet.Name
Rng.Offset(i, 0).Value = i + 1
i = i + 1
End If
Next
End Sub
-----------------------------------------------------------------
Sub ColumnWidth_and_AutoFit_Set()
Columns("A:A").ColumnWidth = 10
Columns("B:B").ColumnWidth = 20

Cells.Select
Selection.Rows.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
Range("A1").Select
End Sub
-----------------------------------------------------------------

Lastly, a question. I like how simple the column width code above
is. I found that by the usual searching in the archives via google.
When I record the process I get the same but with selecting which is
never as safe, I believe, as having the straight code like Columns
(...).ColumnWidth ...

Is there a way to get the horizontal and vertical central alignment
and rows.AutoFit without selecting the cells as well, by any chance?

Thank you. :oD

--

Dave Peterson