Hello,

I have a worksheet that has in column B either numbers, the word "Enter", or
it could be empty. If the cell in column B has a number, it will also have a
data in that row (Column A - AH).

I need a macro that will look at column B to see if there is a number in any
of the cells of column b and if so, I need the macro to copy the data in that
row (columns A - AH) and paste just those rows into a new worksheet.

So for instance, let's say I have 10 rows. In column B, rows 6 - 8 have
numbers; row 9 and 10 has the word "Enter"; row 11 and 12 is empty; and row
13 - 15 have numbers. I need to take only those rows that have numbers in
columin B (6-8 and 13-15) and copy the entire row (columns A-AH) and paste
the data into a new worksheet.

Thanks in advance.

Try this where you fire from the source sheet. Change wks2 to your
destination sheet
Sub copyrowifnumber()
For i = 2 To Cells(Rows.Count, "b").End(xlUp).Row
If Len(Cells(i, "b")) 0 And IsNumeric(Cells(i, "b")) Then
With Sheets("wks2")
dlr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
Rows(i).Copy .Rows(dlr)
End With
End If
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ScottMSP" wrote in message
...
Hello,

I have a worksheet that has in column B either numbers, the word "Enter",
or
it could be empty. If the cell in column B has a number, it will also
have a
data in that row (Column A - AH).

I need a macro that will look at column B to see if there is a number in
any
of the cells of column b and if so, I need the macro to copy the data in
that
row (columns A - AH) and paste just those rows into a new worksheet.

So for instance, let's say I have 10 rows. In column B, rows 6 - 8 have
numbers; row 9 and 10 has the word "Enter"; row 11 and 12 is empty; and
row
13 - 15 have numbers. I need to take only those rows that have numbers in
columin B (6-8 and 13-15) and copy the entire row (columns A-AH) and paste
the data into a new worksheet.

Thanks in advance.

Give this macro a try (change the two Set statements for the Source and
Destination workbooks as appropriate)...

Sub CopyRowsWithNumbersInB()
Dim X As Long
Dim LastRow As Long
Dim Source As Worksheet
Dim Destination As Worksheet
Dim RowsWithNumbers As Range
Set Source = Worksheets("Sheet1")
Set Destination = Worksheets("Sheet2")
With Source
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For X = 2 To LastRow
If IsNumeric(.Cells(X, "B").Value) And .Cells(X, "B").Value < "" Then
If RowsWithNumbers Is Nothing Then
Set RowsWithNumbers = .Cells(X, "B")
Else
Set RowsWithNumbers = Union(RowsWithNumbers, .Cells(X, "B"))
End If
End If
Next
If Not RowsWithNumbers Is Nothing Then
RowsWithNumbers.EntireRow.Copy Destination.Range("A1")
End If
End With
End Sub

--
Rick (MVP - Excel)


"ScottMSP" wrote in message
...
Hello,

I have a worksheet that has in column B either numbers, the word "Enter",
or
it could be empty. If the cell in column B has a number, it will also
have a
data in that row (Column A - AH).

I need a macro that will look at column B to see if there is a number in
any
of the cells of column b and if so, I need the macro to copy the data in
that
row (columns A - AH) and paste just those rows into a new worksheet.

So for instance, let's say I have 10 rows. In column B, rows 6 - 8 have
numbers; row 9 and 10 has the word "Enter"; row 11 and 12 is empty; and
row
13 - 15 have numbers. I need to take only those rows that have numbers in
columin B (6-8 and 13-15) and copy the entire row (columns A-AH) and paste
the data into a new worksheet.

Thanks in advance.

Here is a start
Sub trythis()
mycount = Worksheets("OldData").Range("B:B").Count
For oldcount = 1 To mycount
If IsNumeric(Cells(oldcount, 2)) And Cells(oldcount, 2) < "" Then
newcount = newcount + 1
Rows(oldcount).Copy Worksheets("NewData").Rows(newcount)
End If
Next oldcount
End Sub

If row 1 has headers just change For oldcount = 1 To mycount to For oldcount
= 2 To mycount
You will need to add a worksheet called NewData before running this
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ScottMSP" wrote in message
...
Hello,

I have a worksheet that has in column B either numbers, the word "Enter",
or
it could be empty. If the cell in column B has a number, it will also
have a
data in that row (Column A - AH).

I need a macro that will look at column B to see if there is a number in
any
of the cells of column b and if so, I need the macro to copy the data in
that
row (columns A - AH) and paste just those rows into a new worksheet.

So for instance, let's say I have 10 rows. In column B, rows 6 - 8 have
numbers; row 9 and 10 has the word "Enter"; row 11 and 12 is empty; and
row
13 - 15 have numbers. I need to take only those rows that have numbers in
columin B (6-8 and 13-15) and copy the entire row (columns A-AH) and paste
the data into a new worksheet.

Thanks in advance.

Hi,

I have a very similar problem but want to copy all rows where a numerical
value in a given column is greater than 1, I also want to ensure that column
headings in the destination worksheet are preserved during the paste
operation. How should I modify the code below to achieve that?

Many thanks in advance.

Lu

"Rick Rothstein" wrote:

Give this macro a try (change the two Set statements for the Source and
Destination workbooks as appropriate)...

Sub CopyRowsWithNumbersInB()
Dim X As Long
Dim LastRow As Long
Dim Source As Worksheet
Dim Destination As Worksheet
Dim RowsWithNumbers As Range
Set Source = Worksheets("Sheet1")
Set Destination = Worksheets("Sheet2")
With Source
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For X = 2 To LastRow
If IsNumeric(.Cells(X, "B").Value) And .Cells(X, "B").Value < "" Then
If RowsWithNumbers Is Nothing Then
Set RowsWithNumbers = .Cells(X, "B")
Else
Set RowsWithNumbers = Union(RowsWithNumbers, .Cells(X, "B"))
End If
End If
Next
If Not RowsWithNumbers Is Nothing Then
RowsWithNumbers.EntireRow.Copy Destination.Range("A1")
End If
End With
End Sub

--
Rick (MVP - Excel)

Hello,

I do't know whether this is right place to post this but please help me out.


I have to create a macro which copy rows of date from the source to new
excell sheet based on another excell sheet(user defined conditions)

Example : original excell has fields

Division number Employee name Employee Location

now i have to copy rows where condition meets and replace the employee name
and other fields.

Any help is really appreciated.

Thanks.

Hi,

I have used this code and it works perfectly for what I am after.

I would like to know how to do one more thing.

Ok so I enter a value above 0 into a cell on the source sheet and that gets
copied over to the destination sheet.

If i was then to change the value of that cell to 0 or leave it blank, i
would like it to delete the row that was copied to the destination sheet.

Any help would be greatly appreciated.

Regards,
Dean

"Rick Rothstein" wrote:

Give this macro a try (change the two Set statements for the Source and
Destination workbooks as appropriate)...

Sub CopyRowsWithNumbersInB()
Dim X As Long
Dim LastRow As Long
Dim Source As Worksheet
Dim Destination As Worksheet
Dim RowsWithNumbers As Range
Set Source = Worksheets("Sheet1")
Set Destination = Worksheets("Sheet2")
With Source
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For X = 2 To LastRow
If IsNumeric(.Cells(X, "B").Value) And .Cells(X, "B").Value < "" Then
If RowsWithNumbers Is Nothing Then
Set RowsWithNumbers = .Cells(X, "B")
Else
Set RowsWithNumbers = Union(RowsWithNumbers, .Cells(X, "B"))
End If
End If
Next
If Not RowsWithNumbers Is Nothing Then
RowsWithNumbers.EntireRow.Copy Destination.Range("A1")
End If
End With
End Sub

--
Rick (MVP - Excel)


"ScottMSP" wrote in message
...
Hello,

I have a worksheet that has in column B either numbers, the word "Enter",
or
it could be empty. If the cell in column B has a number, it will also
have a
data in that row (Column A - AH).

I need a macro that will look at column B to see if there is a number in
any
of the cells of column b and if so, I need the macro to copy the data in
that
row (columns A - AH) and paste just those rows into a new worksheet.

So for instance, let's say I have 10 rows. In column B, rows 6 - 8 have
numbers; row 9 and 10 has the word "Enter"; row 11 and 12 is empty; and
row
13 - 15 have numbers. I need to take only those rows that have numbers in
columin B (6-8 and 13-15) and copy the entire row (columns A-AH) and paste
the data into a new worksheet.

Thanks in advance.