none
How to transfer worksheet names to columns RRS feed

  • Question

  • I want to loop through all the sheets in workbook and transfer the name of each to a column. Any suggestions?
    Friday, February 6, 2015 2:56 PM

Answers

  • Hi egrantz,

    For this requirement, you could retrieve current workbook’s all sheets form Workbook.Sheets property. Then iterate these sheets and get the name.

    Dim i As Long
    Dim s As Object
        For Each s In ActiveWorkbook.Sheets
            i = i + 1
            Cells(i, 1) = s.Name
        Next

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 9, 2015 6:43 AM
    Moderator

All replies

  • This is not VBA but is based on VBA code. You should be able to adapt the concept. I'm reading all sheets in a workbook and putting them into an array. You can just take the value and put it into a cell.

      Forall sht In xlWB2.Worksheets
       Redim Preserve ExistingSheetName(s)              
       ExistingSheetName(s) = sht.Name
       s = s + 1
      End Forall

    Friday, February 6, 2015 3:23 PM
  • If you don't have any local (worksheet) names no need to loop, simply

    Range("A1").ListNames

    Otherwise

    Dim i As Long
    Dim nm As Name
        For Each nm In ActiveWorkbook.Names
            i = i + 1
            Cells(i, 2) = nm.Name
            Cells(i, 3) = "'" & nm.RefersTo
        Next


    Saturday, February 7, 2015 12:39 PM
    Moderator
  • Hi egrantz,

    For this requirement, you could retrieve current workbook’s all sheets form Workbook.Sheets property. Then iterate these sheets and get the name.

    Dim i As Long
    Dim s As Object
        For Each s In ActiveWorkbook.Sheets
            i = i + 1
            Cells(i, 1) = s.Name
        Next

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 9, 2015 6:43 AM
    Moderator