software

What I am trying to do is copy column A-D from quot;scheduledquot; worksheet to quot;testquot; worksheet if column X has an quot;Xquot; in the column. Is this possible and if so how is it done.
Enter and run this small macro:Sub Macro1()
Dim r1 As Range
Dim r2 As Range
Dim L As Long

Worksheets(quot;scheduledquot;).Activate

For L = 1 To 65536
If Cells(L, 24).Value = quot;Xquot; Then
Set r1 = Worksheets(quot;scheduledquot;).Range(quot;Aquot;)
Set r2 = Worksheets(quot;testquot;).Range(quot;Aquot;)
r1.Copy r2
Exit Sub
Else
End If
Next

End Sub-- Gary's Studentquot;jkrist46quot; wrote:

gt;
gt; What I am trying to do is copy column A-D from quot;scheduledquot; worksheet to
gt; quot;testquot; worksheet if column X has an quot;Xquot; in the column. Is this
gt; possible and if so how is it done.
gt;
gt;
gt; --
gt; jkrist46
gt;

try this where sheet2 is the source sheet and sheet5 is the destination.
Sub copyifx()
With Sheets(quot;sheet2quot;)
If Application.CountIf(.Columns(quot;xquot;), quot;xquot;) gt; 0 Then _
..Columns(quot;a:dquot;).Copy Sheets(quot;sheet5quot;).Columns(quot;aquot;)
End With
End Sub

--
Don Guillett
SalesAid Software

quot;jkrist46quot; gt; wrote in message
...
gt;
gt; What I am trying to do is copy column A-D from quot;scheduledquot; worksheet to
gt; quot;testquot; worksheet if column X has an quot;Xquot; in the column. Is this
gt; possible and if so how is it done.
gt;
gt;
gt; --
gt; jkrist46
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 quot;scheduledquot; to
quot;testquot; all you really need to check , according to your macro is if there is
an quot;Xquot; (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 quot;Xquot; 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: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;Gary''s Studentquot; gt; wrote in message
...
gt; Enter and run this small macro:
gt;
gt;
gt; Sub Macro1()
gt; Dim r1 As Range
gt; Dim r2 As Range
gt; Dim L As Long
gt;
gt; Worksheets(quot;scheduledquot;).Activate
gt;
gt; For L = 1 To 65536
gt; If Cells(L, 24).Value = quot;Xquot; Then
gt; Set r1 = Worksheets(quot;scheduledquot;).Range(quot;Aquot;)
gt; Set r2 = Worksheets(quot;testquot;).Range(quot;Aquot;)
gt; r1.Copy r2
gt; Exit Sub
gt; Else
gt; End If
gt; Next
gt;
gt; End Sub
gt;
gt;
gt; -- Gary's Student
gt;
gt;
gt; quot;jkrist46quot; wrote:
gt;
gt; gt;
gt; gt; What I am trying to do is copy column A-D from quot;scheduledquot; worksheet to
gt; gt; quot;testquot; worksheet if column X has an quot;Xquot; in the column. Is this
gt; gt; possible and if so how is it done.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; jkrist46
gt; gt;

Originally Posted by jkrist46What I am trying to do is copy column A-D from quot;scheduledquot; worksheet to quot;testquot; worksheet if column X has an quot;Xquot; in the column. Is this possible and if so how is it done.Thanks everyone
You never did tell us what got you to the proper answer.

--
Don Guillett
SalesAid Software

quot;jkrist46quot; gt; wrote in message
...
gt;
gt; jkrist46 Wrote:
gt;gt; What I am trying to do is copy column A-D from quot;scheduledquot; worksheet to
gt;gt; quot;testquot; worksheet if column X has an quot;Xquot; in the column. Is this
gt;gt; possible and if so how is it done.
gt;
gt;
gt; Thanks everyone
gt;
gt;
gt; --
gt; jkrist46

Originally Posted by jkrist46What I am trying to do is copy column A-D from quot;scheduledquot; worksheet to quot;testquot; worksheet if column X has an quot;Xquot; in the column. Is this possible and if so how is it done.It worked in a round about way. I had to do some tweeking due to others changing columns.

Thanks