I have an Excel Spreadsheet with rows of data in several categories.
Each category is separated by two blank rows. In column G, in the blank
cell immediately above each category, I write the category heading - a
process I have already automated using the macro Copy_Title.

To save having to manually run down column G and run Copy_Title in the
appropriate cells, I am now trying to write another macro which will
automate this task. However, my efforts have not achieved success so
far:


Sub Copy_Title_Auto()

Range("G2").Select

Dim LastRow As Long
Dim x As Long
Dim y As Long
y = ActiveCell.Column
LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Application.ScreenUpdating = False
For x = LastRow To 2 Step -1
If Cells(x, y).Value = "" And Cells(x - 1, y).Value = "" Then
With Cells(x, y)
Call Copy_Title
End With
End If
Next x
Application.ScreenUpdating = True

End Sub


Can anyone help please?

Many thanks.

Call Copy_Title

Where's the code for this?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

In article , lid says...

Call Copy_Title

Where's the code for this?

Surely you don't need that? The Copy_Title works perfectly and will do
so when called from this macro?

In article , lid says...

Call Copy_Title

Where's the code for this?

Surely you don't need that? The Copy_Title works perfectly and will
do so when called from this macro?

So how does it know which cell to act on???
Your 'With Cells(x, y) doesn't work because Copy_Title is not a
method of the Cells object...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

In article , lid says...

In article , lid says...

Call Copy_Title

Where's the code for this?

Surely you don't need that? The Copy_Title works perfectly and will
do so when called from this macro?

So how does it know which cell to act on???
Your 'With Cells(x, y) doesn't work because Copy_Title is not a
method of the Cells object...

Thanks. So perhaps that's where I was going wrong...? What I tried to
write was a macro that would go up column G, calling the Copy_Title
macro every time it got to a cell where the cell and the one above were
blank. (This is exactly what I do manually at present!)

Hi,

Am Sun, 27 Mar 2016 10:09:07 +0100 schrieb Victor Delta:

Thanks. So perhaps that's where I was going wrong...? What I tried to
write was a macro that would go up column G, calling the Copy_Title
macro every time it got to a cell where the cell and the one above were
blank. (This is exactly what I do manually at present!)

you don't have to copy. You can refer correctly to your Author. Modify
in the code "your title" to the cell you wanted to copy:

Sub Test()
Dim LastRow As Long, i As Long

LastRow = Cells(Rows.Count, 7).End(xlUp).Row
For i = LastRow To 2 Step -1
If IsEmpty(Cells(i, 7)) And IsEmpty(Cells(i - 1, 7)) Then
Cells(i, 7) = "your title"
End If
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional