none
Insert Worksheet name into column for all worksheets in workbook RRS feed

  • Question

  • Greetings all,

    Appologies if this query has been resolved elsewhere on the forum, I have thus far been able to enter the correct search string in order to locate it.

    I have an excel workbook with 30 worksheets, what I am needing to do is insert a column to the left of the data, and then populate that column with the worksheet name for all rows contained within the worksheet.

    Any suggestions / points?

    Thanks much.

    Thursday, January 22, 2015 11:25 AM

Answers

  •     

    This assumes that "left of the data" means a new column A - if your data starts in D, for example, then A:A and A1 need to change to D:D and D1

    Sub InsertSheetName()
        Dim Sh As Worksheet

        For Each Sh In Worksheets
            Sh.Range("A:A").Insert
            Sh.Range("A1").Resize(Sh.UsedRange.Rows.Count).Value = Sh.Name
        Next
    End Sub


    Thursday, January 22, 2015 1:36 PM

All replies

  •     

    This assumes that "left of the data" means a new column A - if your data starts in D, for example, then A:A and A1 need to change to D:D and D1

    Sub InsertSheetName()
        Dim Sh As Worksheet

        For Each Sh In Worksheets
            Sh.Range("A:A").Insert
            Sh.Range("A1").Resize(Sh.UsedRange.Rows.Count).Value = Sh.Name
        Next
    End Sub


    Thursday, January 22, 2015 1:36 PM
  • Thank you ever so much, seems so simple but when you can't figure it out not the case.

    Thank you again for taking the time to answer.

    Monday, January 26, 2015 11:55 AM