Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying select cells using VB
Hi, Appologies if my title isnt clear but i cant describe what i want t do! I have a large spreadsheet with many rows and wish to extrac certain details (all columns in a row) if a specific feature is foun 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 ne sheet..giving me a new sheet containing all the details for the row that contained an 'a' 'b' or 'c'. I wish to automate this by using a button but have no idea where t start... please please help me ow wise ones! -- jagbabbr ----------------------------------------------------------------------- jagbabbra's Profile: http://www.excelforum.com/member.php...fo&userid=3252 View this thread: http://www.excelforum.com/showthread.php?threadid=52307 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Select Repeating Cells in a Column | Excel Worksheet Functions | |||
Up down arrow keys do not select cells if select locked cells unch | Excel Discussion (Misc queries) | |||
Macro to select cells without a certain value and select a menu it | Excel Worksheet Functions | |||
Using formulas to select cells (Ex: Select every nth cell in a col | Excel Discussion (Misc queries) | |||
how do you "select locked cells" w/o "select unlocked cells"? | Excel Discussion (Misc queries) |