ExcelBanter - Copying select cells using VB

well, saying "etc" leaves a lot to the imagination and little to actually
providing a solution.

at this point, you can use the next available column and put in a formula
like (assume row 2)

=if(sum(countif(h2,{"*a*","*b*","*c*"}))0,"copy", "leave")

so
Sub copydata()
Dim rng As Range, rng1 As Range, rng2 As Range
Dim sh As Worksheet
With Worksheets("Data")
Set rng = .Cells(1, 256).End(xlToLeft)(1, 2)
Set rng1 = .Cells(Rows.Count, "H").End(xlUp)
Set rng2 = .Range(.Cells(2, rng.Column), .Cells(rng1.Row, rng.Column))
rng2.Formula = "=if(sum(countif(h2,{""*a*""" & _
",""*b*"",""*c*""}))0,""copy"",""leave"")"
rng2(1).Offset(-1, 0).Value = "Header10"
rng2.Offset(-1, 0).Resize(rng2.Count, 1).AutoFilter _
Field:=1, Criteria1:="copy"
Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
.AutoFilter.Range.EntireRow.Copy Destination:=sh.Range("A1")
.AutoFilterMode = False
sh.Columns(rng2.Column).Delete
rng2.EntireColumn.Delete
End With
End Sub

Adjust the above code to suit your environment/needs.

Place the macro in a general module (in the vbe, insert=Module)

make the forms toolbar visible, then click on the button and draw it on your
worksheet. Assign the above macro to the button.

--
Regards,
Tom Ogilvy

"jagbabbra" wrote:

Hi,
Appologies if my title isnt clear but i cant describe what i want to
do! I have a large spreadsheet with many rows and wish to extract
certain details (all columns in a row) if a specific feature is found
in the rows.

The feature i am looking for is that if h4..h5..h6.. etc has 'a' or 'b'
or 'c' then i want the entire row of 4,5,6etc to be copied into a new
sheet..giving me a new sheet containing all the details for the rows
that contained an 'a' 'b' or 'c'.

I wish to automate this by using a button but have no idea where to
start... please please help me ow wise ones!

J

--
jagbabbra
------------------------------------------------------------------------
jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525
View this thread: http://www.excelforum.com/showthread...hreadid=523074