PDA

View Full Version : Solved: Cut and copy new approach



rayoconnell
05-15-2007, 02:45 AM
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
05-15-2007, 03:20 AM
You wrote all that code and couldn't do a simple job like this?



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

rayoconnell
05-15-2007, 03:24 AM
You wouldnt beleive how long it took. thanks man, you are a legend.:bow:

Bob Phillips
05-15-2007, 03:28 AM
Well, it looks pretty good, far more complex than the code I gave you.

Aussiebear
05-15-2007, 03:51 AM
Phew...... that not code, that War & Peace rescripted.

rayoconnell
05-15-2007, 03:55 AM
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
05-15-2007, 03:59 AM
Bob is .FIND("Y") case sensitive?, is it posible to do it like this? Set oCell = LCase(rng.Find("Y"))

Bob Phillips
05-15-2007, 04:05 AM
oops! Mis-read the spec



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

Bob Phillips
05-15-2007, 04:08 AM
Bob is .FIND("Y") case sensitive?, is it posible to do it like this? Set oCell = LCase(rng.Find("Y")) Not in itself, but there is a MatchCase property.

rayoconnell
05-15-2007, 04:17 AM
oops! Mis-read the spec
If Not oCell Is Nothing And oCell.Address sFirst Then




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:beerchug: