bring digits of locations for the sheets names in specific column

[abdelfattah]

hello
I have many sheets are different names PUR, SSS ,MAN56 ,JAN12 , so if sheet PUR in first and the SSS is the second … etc. then should bring the all sheets with digits of locations in COL A ,B like this
COL A COL B
LOCATION NAME
1 PUR
2 SSS
3 MAN56
4 JAN12
thanks in advance

 

[Rick Rothstein]

This macro will list the sheet indexes in Column A and the name of the sheet tab in that index position...

Sub SheetIndexesAndNames()
  Dim X As Long
  For X = 1 To Sheets.Count
    Cells(X, "A").Resize(, 2).Value = Array(X, Sheets(X).Name)
  Next
End Sub

 

[abdelfattah]

excellent ! may you also add to your code the headers in row1 LOCATION , NAME ?

 

[Rick Rothstein]

Give this a try...

Sub SheetIndexesAndNames()
  Dim X As Long
  Range("A1:B1") = Split("LOCATION NAME")
  For X = 1 To Sheets.Count
    Cells(X + 1, "A").Resize(, 2).Value = Array(X, Sheets(X).Name)
  Next
End Sub

 

[abdelfattah]

thanks rick that's great ! just I would bold and highlight the headers by yellowy and make the data from row2 highlight by light blue the cells and red the data and make borders like the table and the data should be in center
like this image

de .xlsm
	A	B
1	LOCATION	NAME
2	1	STOCK
3	2	FIRST
4	3	SECOND
5	4	THIRD
6	5	FOURTH
7	6	result
result

thanks again

 

[Rick Rothstein]

Does this do what you want...

Sub SheetIndexesAndNames()
  Dim X As Long
  With Range("A1:B1")
    .Value = Split("LOCATION NAME")
    .Interior.Color = vbYellow
    .BorderAround xlContinuous, xlThin
    .Borders(xlInsideVertical).Weight = xlThin
    .Font.Bold = True
  End With
  For X = 1 To Sheets.Count
    Cells(X + 1, "A").Resize(, 2).Value = Array(X, Sheets(X).Name)
    Cells(X + 1, "A").BorderAround xlContinuous, xlThin
    Cells(X + 1, "B").BorderAround xlContinuous, xlThin
  Next
  With Range("A2:B" & Sheets.Count + 1)
    .Interior.ColorIndex = 37
    .Font.Color = vbRed
  End With
  Columns("A:B").AutoFit
End Sub

 

[abdelfattah]

well done ! thanks for your cooperation