Need help with copy if
 

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

Need help with copy if
 

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:D")
Set r2 = Worksheets("test").Range("A:D")
r1.Copy r2
Exit Sub
Else
End If
Next

End Sub


-- Gary's Student


"jkrist46" wrote:


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


--
jkrist46

Need help with copy if
 

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

--
Don Guillett
SalesAid Software

"jkrist46" wrote in message
...

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


--
jkrist46

Need help with copy if
 

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:D 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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Gary''s Student" wrote in message
...
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:D")
Set r2 = Worksheets("test").Range("A:D")
r1.Copy r2
Exit Sub
Else
End If
Next

End Sub


-- Gary's Student


"jkrist46" wrote:


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


--
jkrist46

Thanks everyone

Need help with copy if
 

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

--
Don Guillett
SalesAid Software

"jkrist46" wrote in message
...

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


Thanks everyone


--
jkrist46

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

Thanks