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

Question

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!

Clarifying: You have 10 sheets and on 1 sheet(Among those 10) you want the names of all other sheets? — , Jan 24 '19 at 10:02
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, Jan 24 '19 at 11:33

score 2 · Accepted Answer · 2019-01-24T14:00:08.777

2

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

edited Jan 24 '19 at 14:00

answered Jan 24 '19 at 10:11

I changed Set nextEntry = .Range("G4") for Set nextEntry = .Range("G" & Rows.Count).End(xlUp).Offset(1) and it works, thanks a lot! – Adren Jan 24 '19 at 11:41
How do I write the code so that it doesn't start filling cells with the same sheet names? i.e. it just needs to be updated with the new ones – Adren Jan 24 '19 at 13:47
I have updated my answer. If that completes your requirement, Could you please accept it as answer – Jan 24 '19 at 14:00
Thanks! For some reason it returns and stores name of only one sheet – Adren Jan 24 '19 at 14:16
Strange! Are you sure that you have more that one "unmentioned" sheet in entire G column? – Jan 24 '19 at 14:18
I rechecked it once again, it seems working but it writes down name of one sheet in the end anyways even though it already exists in the list – Adren Jan 24 '19 at 14:22
How do I make this question "complete"? – Adren Jan 24 '19 at 14:36

Harun24hr · Answer 2 · 2019-01-24T10:33:50.680

1

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.

edited Jan 24 '19 at 10:33

answered Jan 24 '19 at 10:28

Harun24hr

30,391
4
21
36

score 0 · Answer 3 · answered Jan 24 '19 at 11:52

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

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

3 Answers3

Linked