stumped!!

[Guest]

I have this code that adds sheets to my workbook i would like to add each
sheet name that is added to a sheet in my workbook.
sheet name data column y starting at row 2

Private Sub CommandButton3_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim ws As Worksheet

Set ws = wksData
Set LastCell = ws.Cells(Rows.Count, "O").End(xlUp)
Set Rng = ws.Range("O2", LastCell)
For Each cell In Rng
If Not cell.Value = "" Then
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(cell.Value)
On Error GoTo 0
If ws Is Nothing Then
wksMaster.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Visible = True
ActiveSheet.Name = cell.Value & cell.Offset(0, 1).Value

Range("I3").Value = cell.Offset(0, 2).Value
End If
End If
Next

End Sub

 

[JW]

Haven't delved into your code, but this should work. Below your
ActiveSheet.Name line, place something like this:
Dim lRow As Long
With Sheets("data")
lRow = .Cells(.Rows.Count, "y").End(xlUp).Row
.Cells(lRow + 1, "y").Value = ActiveSheet.Name
End With

 

[Guest]

Thank you Jw it worked great

Haven't delved into your code, but this should work. Below your
ActiveSheet.Name line, place something like this:
Dim lRow As Long
With Sheets("data")
lRow = .Cells(.Rows.Count, "y").End(xlUp).Row
.Cells(lRow + 1, "y").Value = ActiveSheet.Name
End With