Unable to write the worksheet name on the respective worksheet

[anuradhagrewal]

Hi Folks
Happy new year
I have these multiple excel sheets in csv format which are name as per the 6 digit employee number for eg : 467733.csv.
Now there are 6 rows starting from A1 to F3000 which lists their working hours for a set of common tasks for a given day.
I import all these csv files into individual worksheets in a single excel file with each worksheet named as for eg 467733.csv etc.
What I need is that in column G2 onwards of all the excel worksheets (in the given excel file) ,the name of the worksheet be automatically be input for the above mentioned range A1 to F3000.
Can anybody suggest a VB script of this.

Please help

Regards

Anu

 

[Alex Blakenburg]

Do you mean just something like this ?

Sub PopulateSheetName()

    Dim ws As Worksheet
    Dim rng As Range
    Dim LastRow As Long
    
    For Each ws In ActiveWorkbook.Worksheets
        With ws
              LastRow = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
              Set rng = .Range("G1:G" & LastRow)
              rng.Value = .Name
        End With
    Next ws
End Sub

 

[anuradhagrewal]

Do you mean just something like this ?

Sub PopulateSheetName()

    Dim ws As Worksheet
    Dim rng As Range
    Dim LastRow As Long
   
    For Each ws In ActiveWorkbook.Worksheets
        With ws
              LastRow = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
              Set rng = .Range("G1:G" & LastRow)
              rng.Value = .Name
        End With
    Next ws
End Sub

Absolutely Brilliant Alex

 

[Alex Blakenburg]

You're welcome. Glad I could help.