Copy Data to New Workbook based on Criteria

Firstly - the original thread is at http://www.ozgrid.com/forum/showthread.php?t=87587

Now the project got a little more complicated. This is what I have been asked to have Excel perform.

-I now have 2 separate workbooks - the original file (Workbook1) where the data will be coming from and workbook 2 that needs to be updated each week - names will remain the same.

-Workbook 2 will have information added each week - the old data will need to be deleted. The only thing that needs to remain is the header in Row 1.

-From the old workbook jobs that read "No RFE" in column N - we would like the data from that Row - columns C-U copied over to workbook 2.

-Save workbook 2

I am fairly new to VBA and have been learning lots of new things, but this is something that I am having trouble with.

Thanks,
Jimbean

This is what I have received from the other board

Option Explicit 
 
Sub No_RFE() 
    Dim c As  Range 
    Dim lRow As Long 
    Dim NewWB As Workbook 
    Dim iCol As Integer 
     
    Application. ScreenUpdating = False 
     
    Set NewWB = Workbooks.Add 
     
     'copy  header and  formats
    For iCol = 1 To 18 
        NewWB. Sheets("Sheet1").Cells(1, iCol).ColumnWidth = Me.Cells(1, iCol + 2).ColumnWidth 
    Next iCol 
     
    Me.Activate 
    Range(Cells(1, 3), Cells(1, 21)).Copy 
    NewWB.Activate 
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Cells(1, 1) 
    Range(Cells(1, 1), Cells(1, 18)).WrapText = True 
     
     
     'copy records with "No Ref" in column N
    Me.Activate 
    lRow = 1 
    For Each c In Range("N1", Range("N65536").End(xlUp)) 
        If c.Value = "No RFE" Then 
            Range(Cells(c.Row, 3), Cells(c.Row, 21)).Copy 
             
            NewWB.Activate 
            lRow = lRow + 1 
            ActiveSheet.Paste Destination:=Worksheets("Sheet1").Cells(lRow, 1) 
            Range(Cells(lRow, 1), Cells(lRow, 18)).WrapText = True 
            Me.Activate 
        End If 
    Next c 
    Application.CutCopyMode = False 
End Sub

Still working on saving file and renaming, as well as sorting the new file by dates.

Jimbean

Hello Jimbean,

It looks as though Bill Rochenbach has covered all your issues completely.

See link here: http://www.ozgrid.com/forum/member.php?u=6027

So, I am closing this post.

Sincerely,
Leith Ross