copying sheet name

[oldjay]

I want to copy the sheet name to row 1 of the first blank column after col 15

Oldjay

 

[JLGWhiz]

If ActiveSheet.Range("O1").Offset(0,1) = "" Then
ActiveSheet.Range("P1") = ActiveSheet.Name
Esle
ActiveSheet.Range("O1").End(xlToRight).Offset(0, 1) = ActiveSheet.Name
End If

 

[Ryan H]

This should help.

Sub FindLastColumn()

Dim lngLastColumn As Long

lngLastColumn = ActiveSheet.Rows(1).Find(What:="", _
After:=Cells(1, 15), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True, _
SearchFormat:=False).Column

Cells(1, lngLastColumn) = ActiveSheet.Name

End Sub

If this helps let me know. Click "YES" below.

 

[JLGWhiz]

corrected:

If ActiveSheet.Range("O1").Offset(0,1) = "" Then
ActiveSheet.Range("P1") = ActiveSheet.Name
Else
ActiveSheet.Range("O1").End(xlToRight).Offset(0, 1) = ActiveSheet.Name
End If

 

[Patrick Molloy]

Sub setsheetname()

With Cells(1, WorksheetFunction.Max(Cells(1,
Columns.Count).End(xlToLeft).Column + 1, 15))
.Value = ActiveSheet.Name
End With

End Sub


go to the rightmost column, then get the END/LEFT column number and add 1,
use 15 or this if its larger