On Sheet1 I have data running from A1-E1 with the number of lines o
data is undetermined so you need to look for the last row.

What I would like to do is check to see if there is a value in B1 or C
and if so- copy the line and move it to Sheet2. I want to do this fo
the remainder of the sheet moving copying lines with values in B or
to the next line on Sheet2.

Any help in code would be very much appreciated

--
sungen9
-----------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...nfo&userid=914
View this thread: http://www.excelforum.com/showthread.php?threadid=54062

heres one way

Option Explicit

Sub CopyData()
Dim wS1 As Worksheet
Dim ws2 As Worksheet
Dim lRow As Long
Dim l4Row As Long
Dim lNxtRow As Long

Set wS1 = Sheets("sheet1")
Set ws2 = Sheets("sheet2")

lRow = wS1.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
lNxtRow = ws2.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
For l4Row = 1 To l4Row Step 1
If wS1.Cells(l4Row, "b").Value < "" _
Or wS1.Cells(l4Row, "c").Value < "" Then
lNxtRow = lNxtRow + 1
wS1.Range("a" & l4Row & ":e" & l4Row).Copy ws2.Cells(lNxtRow, "a")
End If
Next l4Row
End Su

--
mudrake
-----------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...nfo&userid=247
View this thread: http://www.excelforum.com/showthread.php?threadid=54062

Thank you for your quick reply. when i plug in this macro i get stoped
here.

Cant execute code break is the error.

lNxtRow = ws2.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=540628

Selfish bump. :(


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=540628

Perhaps something like this:

Dim rng as Range
with worksheets("sheet1")
set rng = columns(B:C).specialcells(xlconstants)
End with
if not rng is nothing then
rng.entirerow.copy Destination:=Worksheets("Sheet2").Range("A1")
End if

--
Regards,
Tom Ogilvy


"sungen99" wrote:


On Sheet1 I have data running from A1-E1 with the number of lines of
data is undetermined so you need to look for the last row.

What I would like to do is check to see if there is a value in B1 or C1
and if so- copy the line and move it to Sheet2. I want to do this for
the remainder of the sheet moving copying lines with values in B or C
to the next line on Sheet2.

Any help in code would be very much appreciated.


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=540628

Can anyone help me please de-bug either of the codes? I cant seem to ge
either of them to work

--
sungen9
-----------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...nfo&userid=914
View this thread: http://www.excelforum.com/showthread.php?threadid=54062

I tried to use the macro recorder to sort by col B, then by C. It does
what I need to just copy the top X lines that contain data in the col B
and C and move it onto sheet2.

It looks kinda like im right back where I was.

I’m sure this code is difficult and it would figure that I’m actually
doing a favor for someone on this one as apposed to it being for me.

Your help is much appreciated.


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=540628

Being a pest is the last thing I want to be :) and therefore with is my
last bother with this thread. If someone can help me that’s great- I
know I am asking a lot and all with the coding with this. ;)

Thanks again,


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=540628

If sheet 2 is blank then code will error


Try this

Option Explicit

Sub CopyData()
Dim wS1 As Worksheet
Dim ws2 As Worksheet
Dim lRow As Long
Dim l4Row As Long
Dim lNxtRow As Long

Set wS1 = Sheets("sheet1")
Set ws2 = Sheets("sheet2")

lRow = wS1.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
On Error Resume Next
lNxtRow = ws2.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
On Error Goto 0
For l4Row = 1 To l4Row Step 1
If wS1.Cells(l4Row, "b").Value < "" _
Or wS1.Cells(l4Row, "c").Value < "" Then
lNxtRow = lNxtRow + 1
wS1.Range("a" & l4Row & ":e" & l4Row).Copy ws2.Cells(lNxtRow, "a")
End If
Next l4Row
End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=540628

Thank you so much for getting back to me. However when I use the code
in my application nothing happens.

Im wondering if its not doing what im looking for.

Im looking for it to take the data on sheet1 and see if there is a
value in b or c. if there is copy the line from a – f to sheet 2 and
move on until the end of the sheet.

Thanks again for your help in this.


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=540628