Copy rows to new worksheet on condition

[IanMc]

Dear Excelsperts,

I haven't mucked about with Excel in quite a while now and have been asked to do a module in Excel 2000.

When given a workbook (tej-exit.xls) which has one worksheet of thousands of rows with columns from A to AS, i would like to copy all rows which have a zero in column N to a new worksheet.

Is this difficult?
Would i have to have one workbook with the code module in, load up the tej-exit.xls file ?

Thanks for any help

IanMc

 

[motodie]

When I run this it only copies the header row into a newly created results worksheet?

Once I get this working it is exactly what I was looking for...

I googled for way to extract data in almost the same manner and I found this page. I followed the exact instruction and ran it and it only copied the header row like you said. how do you eventually get it working? i will deeply appreciate if you can tell me what modifications have you done?

and my criteria is positive figure ( >0 ) as opposed to zero ( =0 ) hence "almost".

 

[mlangen]

I have the code at work, I will update this thread tomorrow.

 

[motodie]

Thanks. looking forward to your solution..

 

[mlangen]

Send me your email and I will send you the workbook.

Public Sub DefectsStateTrending()
Dim x As Variant
Dim dc1 As String
Dim src As Worksheet
Dim dst As Worksheet
Dim LastR As Range
Set src = Worksheets("Data_Consolidation")
Set dst = Worksheets("Defect Metrics")
x = Application.Match(CLng(Date), dst.Columns(1), 0)
If IsError(x) Then
    Set LastR = dst.Range("a" & Rows.Count).End(xlUp)(2)
Else
    Set LastR = dst.Range("a" & x)
End If
LastR.Value = Date
src.Range("d2:d7").Copy
LastR.Offset(, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
 
 
End Sub

 

[sadi]

thanks

my address is sadi_jebon@yahoo.com

 

[j2dsouza]

Hi i have a spreadsheet( Sheet1) with around 5 thousand rows and 4 columns. I want to be able to copy all the rows which have the word "ISDA" contained in the second column (column B) and paste it into Sheet2. I tried using your code but it didint seem to work for me. Any help would be appreciated.

Thanks

 

[Sandy84]

Hi IanMac

Your code helped me what I was searching for. I just need one help as in your last code if cell N=0 it will open a new sheet and copy the required content.

I need that every time I save me current sheet with making the changes or addining new contents it will automaticaly replace the sheet in place of creating new.

Can you please share me tha code for the same.

Thanks

 

[Sandy84]

Hi IanMac

Your code helped me what I was searching for. I just need one help as in your last code if cell N=0 it will open a new sheet and copy the required content.

I need that every time I save me current sheet with making the changes or addining new contents it will automaticaly replace the sheet in place of creating new.

Can you please share me tha code for the same.

Thanks
<!-- / message -->