ExcelBanter - View Single Post - Excel macro

Victor Delta[_2_]

In article , lid says...

Here's one approach...

Sub CopyTitle()
' To manually set single titles
Get_Title
End Sub

Sub SetTitles()
' To automate setting multiple titles
Dim lLastRow&, n&, lCol&

lCol = Columns("G").Column
lLastRow = Cells(Rows.Count, lCol).End(xlUp).Row

Application.ScreenUpdating = False
For n = 3 To lLastRow
With Cells(n, lCol)
If .Value = "" And .Offset(-1).Value = "" Then
Get_Title Cells(n, lCol)
End If
End With
Next 'n
Application.ScreenUpdating = True

End Sub

Sub Get_Title(Optional Rng As Range)

If Rng Is Nothing Then Set Rng = ActiveCell
Const sAltTitle$ = "Other Activities"

Select Case ActiveSheet.Name
Case "By Category"
With Rng
.Value = .Offset(1, -5) '//set title
If .Value = "zOther Activities" Then .Value = sAltTitle
.Resize(1, 7).Merge
End With 'Rng
Call Format_Titles(Rng)

Case "By Day"
With Rng
.Value = .Offset(1, 0) '//set title
If .Value = "Monthly" Then .Value = sAltTitle
.Resize(1, 7).Merge
End With 'Rng
Call Format_Titles(Rng)

Case "By Location"

Garry

Thank you so much for all your hard work. This looks great. However,
when I run the new CopyTitle macro, it only operates on the cell where
the cursor is - when it's G3, the correct title is inserted for the top
group of data - but it doesn't do anything on the rest of the sheet...?

Should I have mentioned that I'm using Excel 2003 in case that is
relevant?

Finally, the merge line is just to improve the overall appearance of the
data titles.

V