How to copy all sheet names to a particular sheet in the same workbook?

0
votes

I'm kinda new to stackoverflow platform and particularly to VBA, just making my first steps. So I have the following problem: There are several sheets in my workbook and I need to copy names of these sheets except name of one sheet to which I'm copying these names to. Names should be copied to a particular place, too. So far I came up with this:

Sub passport_combining()

Dim i As Worksheet

For Each i In ActiveWorkbook

If i.Name <> "Sheetname_I_dont_need_to_copy_to" Then

    Range("G").Value = Range("G" & Rows.Count).End(xlUp).Offset(1).Select.i.Name

    ActiveSheet.paste

End If    

Next i

End Sub

Code needs to loop through every sheet except the one I'm copying to, derive names from these sheets and copy it to the lastrow + 1 of column G of last sheet I understand technically what I need to do to come up with the solution but I got really stuck in a problem! I even tried to record a macro where I copied sheet names but unfortunately all VBA gives me is bunch of Select options which are advised to be avoided. Any advice would be helpful! Thanks!

3
excelvba
Clarifying: You have 10 sheets and on 1 sheet(Among those 10) you want the names of all other sheets? – user9167318
Hi! Thanks for your reply. To clarify: No, this 1 sheet is not among these 10 sheets but it's in the same workbook. For instance, I have 11 sheets in total and I need to copy names of 10 sheets to 11th sheet – Adren

3 Answers

2
votes

Here is one option:

With ThisWorkbook.Sheets("Summary")
    Set nextEntry = .Range("G4")
    For Each wsheet In ThisWorkbook.Sheets
        If wsheet.Name <> "Summary" Then
            nextEntry.Value = wsheet.Name
            Set nextEntry = nextEntry.Offset(1, 0)
        End If
    Next wsheet
End With

Where "Summary" is the name of sheet on which you want all the names and .Range("G4") is the starting place for data

Update: Below code will check for value in "Column G" before inserting Sheet Names

Ref: https://stackoverflow.com/a/12648557/9167318

With ThisWorkbook.Sheets("Summary")
    For Each wsheet In ThisWorkbook.Sheets
        If wsheet.Name <> "Summary" Then
            Set nextEntry = .Cells(.Rows.Count, "G").End(xlUp).Offset(1, 0)
            If IsError(Application.Match(wsheet.Name, .Range("G:G"), 0)) Then nextEntry.Value = wsheet.Name
        End If
    Next wsheet
End With
0
votes

In simple try following

Sub AllSheets()
Dim sht As Worksheet
Dim i As Long

    i = 1
    For Each sht In ThisWorkbook.Worksheets
        If sht.Name <> ActiveSheet.Name Then
            Cells(i, "G") = sht.Name
            i = i + 1
        End If
    Next sht

End Sub

Then adjust codes for your need.

0
votes

The below code import all sheets name in Sheet 1 column A.

Option Explicit

Sub Get_Sheets_Name()

    Dim ws As Worksheet
    Dim Lastrow As Long

    'Loop all sheets in this workbook
    For Each ws In ThisWorkbook.Worksheets

        With ThisWorkbook.Worksheets("Sheet1")
            'Find of Sheet1 & Column A lastrow
            Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

            'Import Sheet name in Sheet 1 & Column A
            .Cells(Lastrow + 1, 1).Value = ws.Name

        End With

    Next

End Sub