Copy Worksheet Name for every sheet in workbook and paste in master sheet

[xceln00b]

Excel vba noob here,
I have 10 sheets in a workbook, I want my code to create a new sheet called "Master"; and for each sheet in the workbook, I want the sheet name in the cell A2, A3 , A4 etc in the "Master" sheet.

I want the output to look like the below for the "Master" sheet:
Cell A2 - Sheet1 Name
Cell A3 - Sheet2 Name
Cell A4 - Sheet3 Name

... and so on

The code i have so far is

Sub CopyIt()

Dim ws As Worksheet, x As Long


Application.ScreenUpdating = False


Worksheets.Add.Name = "Master"
Sheets("Master").Range("A1").Value = "Associate Name"


    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Master" Then
            x = x + 1
            ws.Name.Copy 'This is where the code is going wrong. I think i need to fix it here somehow.
            Sheets("Master").Range("A1").Offset(x).PasteSpecial xlPasteValues
            
        End If
    Next


Columns.AutoFit


Application.ScreenUpdating = True


End Sub

I figured it's probably a piece of cake for you pros

Your help is much appreciated!!

 

[JoeMo]

You are close, but you don't want the copy bit. Try this instead:

Sub CopyIt()
Dim ws As Worksheet, x As Long
Application.ScreenUpdating = False
On Error Resume Next  'In case sheet Master already exists
Application.DisplayAlerts = False
Worksheets("Master").Delete
On Error GoTo 0
Worksheets.Add.Name = "Master"
Sheets("Master").Range("A1").Value = "Associate Name"
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Master" Then
        x = x + 1
        Sheets("Master").Range("A1").Offset(x) = ws.Name
    End If
Next ws
Columns("A").AutoFit
With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
End Sub

 

[xceln00b]

omg. WOW!

words cannot express my gratitude, nor can it express how impressed I am

Learned something new today! thank you!!!