Hi Chris,
The sub ProcessA. In the last lines it changes the 5 values in the source
to 0. Not sure if you wanted it to be done in the Source or the newly
inserted rows.

Sub test()
'search 5 in col B of Active sheet and insert into row 2 of worksheets(2)
processA activesheet, worksheets(2), 5
End Sub

'ProcessA :
' Searches value ValueToFind in source worksheet WshS, insert these rows
' (rgResult) in destination sheet WshD row 2. Finally set source to zero
Sub ProcessA(WshS As Worksheet, WshD As Worksheet, ValueToFind As Variant)
Dim rgS As Range, RgD As Range 'range source and destination
Dim found As Boolean, firstAddress As String
Dim rg As Range, rgResult As Range
Dim ttlRows As Long, i As Long

Set rgS = Application.Intersect(WshS.Columns(2), WshS.UsedRange)
Set RgD = WshD.Rows(2)

'Find all cells containing ValueToFind
Set rg = rgS.Find(ValueToFind, , xlValues, xlWhole)
If Not rg Is Nothing Then
firstAddress = rg.Address
Set rgResult = rg
Do
Set rgResult = Application.Union(rgResult, rg)
Set rg = rgS.FindNext(rg)
Loop While Not rg Is Nothing And rg.Address < firstAddress
End If

'Insert into rgD and reset ValuesToFind in RgResult
If Not rgResult Is Nothing Then
RgD.Resize(rgResult.Cells.Count).Insert
rgResult.EntireRow.Copy WshD.Rows(2)
'Which one set to zero? in the source
rgResult.Value = 0
End If

End Sub
'---------------------------------------------

Regard,
Sebastien

"Chris Bromley" wrote:

Hi All,

I have a large number of Excel files in several folders that I need to
perform the following task in. I know how to write VBA code to move from file
to file in order to apply the task, but I cant figure out how to write a
program to do the task itself.

Each file contains three columns of coordinates (in columns A, B & C) , with
the headings X, Y & Z in row 1 of the three columns. The first few rows all
contain the number 5 in column B. I need to copy all of these rows and then
paste them starting in row 2 without overwriting any of the data in the
original rows, i.e. I need to insert a number of empty rows equal to the
number of copied rows starting in row 2. Finally, I need to change all the
fives in the PASTED data to zeros.

The problem is that the number of rows with 5 in column B varies from file
to file, and I cant figure out a way to deal with this variation in my code.

Any help would be greatly appreciated!

Many thanks,

Chris Bromley