Clear row contents and copy row contents.

**fatalcore** · 06-19-2011, 08:14 AM

I am wondering if two seperate macros are possible that

1>Copy the entire Row if the Column C Contains "Completed" in a new sheet called "Done"
I mean will append the new rows if the macro is run again. Without duplicating and update if already existed. Column B contains unique serial number.

another macro when run

2>That will clear the contains from Row C to M if the Column C Contains "Completed" in the entire sheet.

Any help is much appreciated.
Thanks in advance,

**mohd9876** · 06-19-2011, 08:30 AM

could you post an example workbook with some dummy data to show what exactly you need

**fatalcore** · 06-19-2011, 09:16 AM

I have uploaded as required.
Thanks in advnce.

**Roger Govier** · 06-19-2011, 09:52 AM

Hi

No need for 2 macros, it can all be completed in one.
See attached workbook


Sub MoveCompletedData()
    Dim lr1 As Long, lr2 As Long
    Dim ws1 As Worksheet, ws2 As Worksheet

    Set ws1 = Sheets("All Data")
    Set ws2 = Sheets("Done")
    Application.ScreenUpdating = False
    lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1

    ws1.Range("$A$1:$E$1").AutoFilter Field:=3, Criteria1:="Completed"

    ws1.Range("A2:E" & lr1).SpecialCells(xlCellTypeVisible).Copy ws2.Range("A" & lr2)

    Application.CutCopyMode = False
    Range("A2:E" & lr1).SpecialCells(xlCellTypeVisible).ClearContents
    ws1.Range("$A$1:$E$19").AutoFilter Field:=3
    ws1.AutoFilter.Sort.SortFields.Clear
    ws1.AutoFilter.Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending
    With ws1.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
         .Apply
    End With
    ws1.Range("A2").Select
    Application.ScreenUpdating = True
End Sub

**fatalcore** · 06-19-2011, 11:11 AM

It's working .
But when I am clicking the button. it's creating a filter. Which I dont need.

The data are moving perfectly,But the button is still pressed once clicked.

When clicked again giving error and hiding the rows in All Data.I mean if there is no Record to update it is giving the error.

Also it will be great If there can be a button to copy the "going " entries in a new sheet Called "Progress". So that I can see the entries which is still on going.

That's all.

Thanks for everything Roger.

**Roger Govier** · 06-19-2011, 12:32 PM

Hi

use this modified code


Sub MoveCompletedData()
    Dim lr1 As Long, lr2 As Long
    Dim ws1 As Worksheet, ws2 As Worksheet

    Set ws1 = Sheets("All Data")
    Set ws2 = Sheets("Done")
     If WorksheetFunction.CountIf(ws1.Range("C:C"), "Completed") = 0 Then
    
    MsgBox "No Complteded entries to move"
    Exit Sub
    Else
    Application.ScreenUpdating = False
    lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
   
    
    ws1.Range("$A$1:$E$1").AutoFilter Field:=3, Criteria1:="Completed"

    ws1.Range("A2:E" & lr1).SpecialCells(xlCellTypeVisible).Copy ws2.Range("A" & lr2)

    Application.CutCopyMode = False
    Range("A2:E" & lr1).SpecialCells(xlCellTypeVisible).ClearContents
    ws1.Range("$A$1:$E$1").AutoFilter Field:=3
    ws1.AutoFilter.Sort.SortFields.Clear
    ws1.AutoFilter.Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending
    With ws1.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
         .Apply
    End With
    ws1.Range("A2").Select
    End If
    ws1.Range("A1:E1").AutoFilter
    Application.ScreenUpdating = True
End Sub

**fatalcore** · 06-19-2011, 01:08 PM

Awesome Bro !
It's perfect. 5 Star !!!

Clear row contents and copy row contents.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Clear row contents and copy row contents.

Re: Clear row contents and copy row contents.

Re: Clear row contents and copy row contents.

Re: Clear row contents and copy row contents.

Re: Clear row contents and copy row contents.

Re: Clear row contents and copy row contents.

Re: Clear row contents and copy row contents.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions