extract name of worksheets and paste it in another workbook

Question

Can't develop a code that can loop through the worksheets extract the name of each worksheet and then paste it in a master workbook, this is what I've got so far

Sub date()
Dim day As String
Dim x As Integer
Dim wb As Workbook
Set wb = Workbooks.Open(SelectedFileItem)
Dim contador As Integer
contador = 2
Dim result As Workbook
Set result = Workbooks.Open("C:\Users\apractica\Desktop\Macro\Durst\Resultados.xlsm")

For x = 1 To ws_num
    wb.Worksheets(x).Activate
    day = wb.Sheets(x).Name
    result.Cells(contador, 1).PasteSpecial xlPaste
    contador = contador + 1
Next
End Sub

wb = is the workbook with the worksheets I need to extract the name of each

result = is the master workbook where I want to copy the name of each worksheet

First step: Add Option Explicit to the top of the module. ws_num is not declared. — BigBen
For Each ws in wb.Worksheeets, result.Worksheets(1).Cells(contador, 1).Value = ws.Name. — BigBen
Dim ws_num As Integer ws_num = ActiveWorkbook.Worksheets.Count — gmark

BigBen BigBen · Accepted Answer · 2020-03-04T18:11:13

To close this question out:

Add Option Explicit to the top of the module. ws_num is not declared.
Loop over the collection of wb.Worksheets.
Make sure that you specify the worksheet before .Cells(contador, 1). result is a Workbook.

For Each ws in wb.Worksheeets
    result.Worksheets(1).Cells(contador, 1).Value = ws.Name
    contador = contador + 1
Next

extract name of worksheets and paste it in another workbook

1 Answers