Tab Name as column Name

**swathidas** · 01-18-2012, 05:57 AM

Hi All,

I have some Excel sheets in E:\DLB folder, for some Excel files there are two worksheets for some three worksheets and for some four worksheets. The number of worksheets are not fixed. I want to insert the worksheet name as column data for all the worksheets present.

Please suggest me a logic, how can I do this.

Thanks,
Swathi

**arlu1201** · 01-18-2012, 07:39 AM

In which column should the sheet name be copied? All cells or just one particular cell of the column?

**swathidas** · 01-18-2012, 10:18 AM

The sheet name should be copied to column M for all rows.

Thanks,
Swathi

**arlu1201** · 01-26-2012, 07:00 AM

Ok, i will have something for you shortly.

**arlu1201** · 01-26-2012, 08:11 AM

Use this code -

Option Explicit

Dim CurrentFileName As String
Dim myPath As String
Dim lrow As Long
Dim i As Long

Sub cons_data()

'The folder containing the files to be recap'd
myPath = "D:\Test" 'INPUT YOUR PATH HERE

'Finds the name of the first file of type .xls in the current directory
CurrentFileName = Dir(myPath & "\*.xls")

'Create a workbook for the recap report

Do
    Workbooks.Open (myPath & "\" & CurrentFileName)
    For i = 1 To Workbooks(CurrentFileName).Worksheets.Count
        With Workbooks(CurrentFileName).Worksheets(i)
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("M1:M" & lrow).Value = .Name
        End With
    Next i
       
    Workbooks(CurrentFileName).Close (True)
    
'Calling DIR w/o argument finds the next .xlsx file within the current directory.
CurrentFileName = Dir()
Loop While CurrentFileName <> ""

End Sub