Sub AddName()
Dim sAddr As String
Dim v() As Range, i As Long
Dim rng As Range, rng2 As Range
Dim rng1 As Range, rng3 As Range
Set rng = Columns(2).Find( _
What:="Name", _
After:=Cells(Rows.Count, 2), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
ReDim v(1 To 1)
If Not rng Is Nothing Then
sAddr = rng.Address
Do
Set v(UBound(v)) = rng
ReDim Preserve v(1 To UBound(v) + 1)
Set rng = Columns(2).FindNext(rng)
Loop While rng.Address < sAddr
Set v(UBound(v)) = Cells(Rows.Count, 2).End(xlUp)(2)
For i = 1 To UBound(v) - 1
Set rng1 = Range(v(i), v(i + 1))
Set rng2 = Nothing
On Error Resume Next
Set rng2 = rng1.SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
If Not rng2 Is Nothing Then
Set rng3 = Intersect(rng2.EntireRow, Columns(1))
rng3.Value = v(i).Offset(0, 1)
End If
Next
End If
End Sub

worked for me with the data you show and what you described.

--
Regards,
Tom Ogilvy


"Stuart" wrote:

I have data in excel. I have a combine macro to combine a hundred
spreadsheets in excel.

This works no problem however...............

The data at the moment in excel looks like,



Name SW
Report : 6



Item Price in Currency Description
Amount1 Amount2
101101 GBP Fridge 1000.00 1000.00
170101 GBP Washing Machine 200.00 200.00
201001 GBP Fund Transfer In 300.00 300.00

201002 GBP 100.00 100.00
201151 GBP 100.00 100.00
211111 GBP 200.00 200.00
311501 GBP 200.00 200.00
402001 GBP 200.00 200.00

Name JB
19/09/2005 00:00 Currency GBP Pound Sterling Report :
6 7.00



Item Price in Currency Description
Amount1 Amount2
101101 GBP Fridge 1000.00 1000.00
170101 GBP Washing Machine 200.00 200.00
201001 GBP Fund Transfer In 300.00 300.00

201002 GBP 100.00 100.00
201151 GBP 100.00 100.00
211111 GBP 200.00 200.00
311501 GBP 200.00 200.00
402001 GBP 200.00 200.00


What I need from a macro is that everytime it finds the word "Name" in
column B it will pick up the cell to the right of that and then paste
it down the account numbers until it sees the word item and then does
the same again.

The expected output from the macro working would be :

Name SW
Report : 6



Item Price in Currency Description
Amount1 Amount2
SW101101 GBP Fridge 1000.00 1000.00
SW170101 GBP Washing Machine 200.00 200.00
SW201001 GBP Fund Transfer In 300.00 300.00
SW
SW201002 GBP 100.00 100.00
SW201151 GBP 100.00 100.00
SW211111 GBP 200.00 200.00
SW311501 GBP 200.00 200.00
SW402001 GBP 200.00 200.00
SW
Name JB
19/09/2005 00:00 Currency GBP Pound Sterling Report :
6 7.00



Item Price in Currency Description
Amount1 Amount2
JB101101 GBP Fridge 1000.00 1000.00
JB170101 GBP Washing Machine 200.00 200.00
JB201001 GBP Fund Transfer In 300.00 300.00
JB
JB201002 GBP 100.00 100.00
JB201151 GBP 100.00 100.00
JB211111 GBP 200.00 200.00
JB311501 GBP 200.00 200.00
JB402001 GBP 200.00 200.00

Sorry if this looks a mess but as long as the macro starts and stops
and then starts again this should work however I think this will be a
big challenge......I spent around 3 days trying to do this using
recorded macro but it just wont work so I give up!

Hope someone can help,

Kind Regards,

Stu