ExcelBanter - View Single Post - Spreadsheeting Problem

BOHICA

(Kris Taylor) wrote in message . com...
Hello all,

Here is my dilemna:

I am using Excel 97'. I need an excel formula that will look in
column J for an "x". If there's an "x" in column J, I want the
formula to copy and paste that entire row below the formula cell(would
be situated under all of the data). I would need it to loop and do
this copy and paste each row that has an occurence of X in the
appropriate column. Can this be done in excel, or would this need to
be done in VB? If so, could you post some type of macro that would
help me?!

Thanks in advance for your help!

Thanks,

Kris Taylor

I believe a macro is in order. I did a project that basically did
this exact thing. Try this:

Sub NewMacro()

Dim MyCount
Dim Stopit
Dim backtome As String
Dim zInsertz As String
zInsertz = "A" & "32" 'insert row number where you want it inserted
Stopit = "25" 'where you want macro to stop
Stopit = Stopit - 1

Range("J1").Select
MyCount = 1
Loopme:
If ActiveCell.Value = "X" Or ActiveCell.Value = "x" Then
Selection.EntireRow.Copy
Range(zInsertz).Select
Selection.Insert Shift:=xlDown

End If
backtome = "J" & MyCount
Range(backtome).Select
ActiveCell.Offset(1, 0).Activate
If MyCount Stopit Then
GoTo KillBill
End If
MyCount = MyCount + 1
GoTo Loopme

KillBill:
End Sub