Need help with copy if

[jkrist46]

What I am trying to do is copy column A-D from "scheduled" worksheet t
"test" worksheet if column X has an "X" in the column. Is thi
possible and if so how is it done

 

[Gary''s Student]

Enter and run this small macro:


Sub Macro1()
Dim r1 As Range
Dim r2 As Range
Dim L As Long

Worksheets("scheduled").Activate

For L = 1 To 65536
If Cells(L, 24).Value = "X" Then
Set r1 = Worksheets("scheduled").Range("A")
Set r2 = Worksheets("test").Range("A")
r1.Copy r2
Exit Sub
Else
End If
Next

End Sub


-- Gary's Student

 

[Don Guillett]

try this where sheet2 is the source sheet and sheet5 is the destination.
Sub copyifx()
With Sheets("sheet2")
If Application.CountIf(.Columns("x"), "x") > 0 Then _
..Columns("a:d").Copy Sheets("sheet5").Columns("a")
End With
End Sub

 

[David McRitchie]

as written this macro would take 3 minutes to run on a slower system
(600kHz, 128M Ram). With minor revision it should run in well under a
second with a few hundred rows of data.

You should not be using a fixed number for number of rows in a worksheet,
and you should be at least limiting the range to the used range.
In other words you should not be testing rows beyond the data that you have.

Since you do the same thing, copy all of column A form "scheduled" to
"test" all you really need to check , according to your macro is if there is
an "X" (capitalized) in any cell in Column X., which could be done without
any loop, though I doubt this is what the poster really had in mind. (apparently
the specifications are ambiguous, because that would be one interpretation).
Since Don chose that criteria also maybe that is what was really meant. .

If only rows that had the "X" in them were to be copied, then a filter might be
used and then copy the filtered list to the second sheet.

 

[jkrist46]

What I am trying to do is copy column A-D from "scheduled" worksheet t
"test" worksheet if column X has an "X" in the column. Is thi
possible and if so how is it done.

Thanks everyon

 

[Don Guillett]

You never did tell us what got you to the proper answer.

 

[jkrist46]

What I am trying to do is copy column A-D from "scheduled" worksheet t
"test" worksheet if column X has an "X" in the column. Is thi
possible and if so how is it done.

It worked in a round about way. I had to do some tweeking due t
others changing columns.

Thank