ExcelBanter - View Single Post - Excel macro

Victor Delta[_2_]

In article , lid says...

Tested example...

Sub Copy_Title_Auto()
' Run to automate setting titles
Dim lLastRow&, n&, lCol&

lCol = Range("G2").Column
lLastRow = Cells(Rows.Count, lCol).End(xlUp).Row

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

End Sub

Sub Copy_Title(Optional Rng As Range)
' Dummy routine to imitate whatever this does

If Rng Is Nothing Then Set Rng = ActiveCell
Rng.Value = "Title goes here"

End Sub

Sub CopyTitle_OneTime()
' Run to manually set titles
Copy_Title
End Sub

Many thanks Garry. Have played around with this today but can't get it
running quite as I want. At present, the new macro runs well but only
inserts "Title goes here" in the appropriate cells. The modified code
now looks like this (see notes below):

Sub Copy_Title_Auto()
' Run to automate setting titles

Dim lLastRow&, n&, lCol&

lCol = Range("G2").Column
lLastRow = Cells(Rows.Count, lCol).End(xlUp).Row

Range("G3").Select
Application.ScreenUpdating = False
For n = lLastRow To 2 Step -1
With Cells(n, lCol)
If .Value = "" And .Offset(-1).Value = "" Then
Copy_Title Cells(n, lCol)
End If
End With
Next n
Application.ScreenUpdating = True

End Sub

Sub Copy_Title(Optional Rng As Range)
' Dummy routine to imitate whatever this does

Dim s As String
s = ActiveSheet.Name
If s = "By Category" Then Call Copy_Cat_Name
If s = "By Day" Then Call Copy_Day_Name
If s = "By Location" Then Call Copy_Loc_Name

If Rng Is Nothing Then Set Rng = ActiveCell
Rng.Value = "Title goes here"

End Sub

I inserted the "Range("G3").Select" line to stop macro also displaying
"Title goes here" in whatever cell is selected before the macro is run.
(G3 is always the top title box on each sheet).

The 2nd macro now includes the same code as the Copy_Title macro.

Any ideas please? Thanks.