Solved: Cut and copy new approach

[rayoconnell]

Hello world,

OK i was trying the copy and paste thing yesterday but i think its beyond my abilities, anyway i think there is a better way to do it.
If you view the attached file you will see column H,I,J contain Y in some cases. what i want to do is, create a loop that will run down trough column H first and for each instance of "Y" cut and copy that entire row to a new sheet. Again do the same for column I and the same for J.
therefore i will have 4 sheets. the original, 1 for column H,1 for column I and 1 for column J.
I am a beginer to VBA and dont really have clue so any pointers would be great.
thanks in advance.

[Bob Phillips]

You wrote all that code and couldn't do a simple job like this?

[vba]

Sub ProcessData()
Lookat Columns("H:H")
Lookat Columns("I:I")
Lookat Columns("J:J")
End Sub

Private Sub Lookat(rng As Range)
Dim oCell As Range
Dim sh As Worksheet
Dim this As Worksheet

Set this = ActiveSheet
Set oCell = rng.Find("Y")
If Not oCell Is Nothing Then
Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
oCell.EntireRow.Copy sh.Range("A1")
End If
this.Activate
End Sub
[/vba]

[rayoconnell]

You wouldnt beleive how long it took. thanks man, you are a legend.

[Bob Phillips]

Well, it looks pretty good, far more complex than the code I gave you.

[Aussiebear]

Phew...... that not code, that War & Peace rescripted.

[rayoconnell]

just a lot of macros and links to other progs really.
just started a new job and was suppose to be doing DeltaV but ended up doing VBA which i have never done before. really thrown in at the deep end.
i just ran your code and it kinda works,
it sets up 3 new sheets as it is suppose to and adds the first instance of each case. i need it to find all instances. i assume i need to loop trough but again no idea.

[Simon Lloyd]

Bob is .FIND("Y") case sensitive?, is it posible to do it like this? Set oCell = LCase(rng.Find("Y"))

[Bob Phillips]

oops! Mis-read the spec

[vba]

Sub ProcessData()
Lookat Columns("H:H")
Lookat Columns("I:I")
Lookat Columns("J:J")
End Sub

Private Sub Lookat(rng As Range)
Dim oCell As Range
Dim sh As Worksheet
Dim this As Worksheet
Dim sFirst As String
Dim iNextRow As Long

Set this = ActiveSheet
Set oCell = rng.Find("Y")
If Not oCell Is Nothing Then
Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
oCell.EntireRow.Copy sh.Range("A1")
sFirst = oCell.Address
iNextRow = 1
Do
Set oCell = rng.FindNext(oCell)
If Not oCell Is Nothing And oCell.Address sFirst Then
iNextRow = iNextRow + 1
oCell.EntireRow.Copy sh.Range("A" & iNextRow)
End If
Loop Until oCell Is Nothing Or oCell.Address = sFirst
End If
this.Activate
End Sub
[/vba]

[Bob Phillips]

Bob is .FIND("Y&quot case sensitive?, is it posible to do it like this? Set oCell = LCase(rng.Find("Y&quot)

Not in itself, but there is a MatchCase property.

[rayoconnell]

oops! Mis-read the spec
If Not oCell Is Nothing And oCell.Address sFirst Then

[vba]

[/vba]

that worked perfect.
the only problem i had was the above line.
i removed oCell.address and it worked fine.
i hope it wasnt important!!!!
again tahnks a million for your help.
dont know what id do without it