Macro-if condition met-copy and paste row to new sheet

[ScottMSP]

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.

 

[Don Guillett]

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

 

[Rick Rothstein]

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

 

[Bernard Liengme]

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

 

[LuMai]

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

 

[kishore.]

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.

 

[Subliminal]

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

 

[Kiff]

Hey Don Guillett,

I think i might need help with a similar macro. I have been working on a
report that shows values in cells that i need to record. At the moment i
either copy and paste these values or write them down. I'm quite new to
macros but after searching on google it may be posible to get on to do what i
want. Basically i have a main spreadsheet that is fed from other sheets of
information using vlookup. A1 being the key cell value, when i enter a number
in here i get my required information from the other sheets, i then enter
some values in column "J2:J14" the cell values are always numbers. What i
wanted to do is to be able to save the values of J2:J14 that correspond to
the value in A1 on another sheet in the work book. I hope i explained it
clearly and hope you can offer some help.

Thanks
Kiff