Moving rows of data to another sheet with vba

Hello - - I have an excel file that contains about 1000 rows of data, from column A to O. Column C contains either the letter A or the letter I, A means Active, I means Inactive.

What I'd like to do is replace my monthly manual task of moving all the I's to sheet2. When completed, the excel file should have two sheets, all of the A's on one, and the I's in the other. The original excel file is not sorted by column C. The end result should have the same row 1, being the header row.

Any suggested VBA code to get this started would be great! There are some additional steps, to save the file to a specific location but I think I could do that once the excel file is formatted the way I wanted it....

Thanks again for your help....!

Hey all - - Thanks for all your replies, I did however find a workable solution from Ron B.'s site....here it is, a bit longer then other suggestions - - I did add in the save and copy commands at the end:

Sub Copy_To_Worksheets()
'Note: This macro use the function LastRow
    Dim My_Range As Range
    Dim FieldNum As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim ws2 As Worksheet
    Dim Lrow As Long
    Dim cell As Range
    Dim CCount As Long
    Dim WSNew As Worksheet
    Dim ErrNum As Long

    'Set filter range on ActiveSheet: A1 is the top left cell of your filter range
    'and the header of the first column, D is the last column in the filter range.
    'You can also add the sheet name to the code like this :
    'Worksheets("Sheet1").Range("A1:D" & LastRow(Worksheets("Sheet1")))
    'No need that the sheet is active then when you run the macro when you use this.
    Set My_Range = Range("A1:O2000") '  & LastRow(ActiveSheet))
    My_Range.Parent.Select

    If ActiveWorkbook.ProtectStructure = True Or _
       My_Range.Parent.ProtectContents = True Then
        MsgBox "Sorry, not working when the workbook or worksheet is protected", _
               vbOKOnly, "Copy to new worksheet"
        Exit Sub
    End If

    'This example filters on the first column in the range(change the field if needed)
    'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
    FieldNum = 3 ' I changed this to 3 for column C

    'Turn off AutoFilter
    My_Range.Parent.AutoFilterMode = False

    'Change ScreenUpdating, Calculation, EnableEvents, ....
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView
    ActiveSheet.DisplayPageBreaks = False

    'Add a worksheet to copy the a unique list and add the CriteriaRange
    Set ws2 = Worksheets.Add

    With ws2
        'first we copy the Unique data from the filter field to ws2
        My_Range.Columns(FieldNum).AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=.Range("A1"), Unique:=True

        'loop through the unique list in ws2 and filter/copy to a new sheet
        Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
        For Each cell In .Range("A2:A" & Lrow)

            'Filter the range
            My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _
             Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"), "?", "~?")

            'Check if there are no more then 8192 areas(limit of areas)
            CCount = 0
            On Error Resume Next
            CCount = My_Range.Columns(1).SpecialCells(xlCellTypeVisible) _
                     .Areas(1).Cells.Count
            On Error GoTo 0
            If CCount = 0 Then
                MsgBox "There are more than 8192 areas for the value : " & cell.Value _
                     & vbNewLine & "It is not possible to copy the visible data." _
                     & vbNewLine & "Tip: Sort your data before you use this macro.", _
                       vbOKOnly, "Split in worksheets"
            Else
                'Add a new worksheet
                Set WSNew = Worksheets.Add(After:=Sheets(Sheets.Count))
                On Error Resume Next
                WSNew.Name = cell.Value
                If Err.Number > 0 Then
                    ErrNum = ErrNum + 1
                    WSNew.Name = "Error_" & Format(ErrNum, "0000")
                    Err.Clear
                End If
                On Error GoTo 0

                'Copy the visible data to the new worksheet
                My_Range.SpecialCells(xlCellTypeVisible).Copy
                With WSNew.Range("A1")
                    ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
                    ' Remove this line if you use Excel 97
                    .PasteSpecial Paste:=8
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                    .Select
                End With
            End If

            'Show all data in the range
            My_Range.AutoFilter Field:=FieldNum

        Next cell

        'Delete the ws2 sheet
        On Error Resume Next
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
        On Error GoTo 0

    End With

    'Turn off AutoFilter
    My_Range.Parent.AutoFilterMode = False

    If ErrNum > 0 Then
        MsgBox "Rename every WorkSheet name that start with ""Error_"" manually" _
             & vbNewLine & "There are characters in the name that are not allowed" _
             & vbNewLine & "in a sheet name or the worksheet already exist."
    End If

    'Restore ScreenUpdating, Calculation, EnableEvents, ....
    My_Range.Parent.Select
    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
    
    MsgBox ("Copy Complete - - Now saving file to T:\Job Code folder")
    
    FileCopy "T:\Job Codes\Active Job Codes.xls", _
"T:\Job Codes\Archive\Active Job Codes " & Format(Now(), "yyyy - mm") & ".xls"
    
    ActiveWorkbook.SaveCopyAs Filename:="T:\Job Codes\Active Job Codes" & ".xls"
MsgBox ("Active Job Code Now Saved the T Drive")


End Sub

Thanks again...I'm closing this with Solved!!!

Hello ironman,

This macro will search "Sheet1" column "C" for cells with "I" and copy them to "Sheet2". After they are copied, the rows on "Sheet1" will deleted.

Sub SeparateInactiveRows()

  Dim Cell As Range
  Dim DelRng As Range
  Dim DstWks As Worksheet
  Dim R As Long
  Dim Rng As Range
  Dim RngEnd As Range
  Dim SrcWks As Worksheet
  
   'Starting row on destination sheet
    R = 2
    
   'Worksheet were the inactive rows will be copied to
    Set DstWks = Worksheets("Sheet2")
    
   'Source data worksheet
    Set SrcWks = Worksheets("Sheet1")
    
   'Find the last entry in column "C"
    Set Rng = SrcWks.Range("A2:O2")
    Set RngEnd = SrcWks.Cells(Rows.Count, Rng.Columns(3)).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, SrcWks.Range(Rng, RngEnd))
    
     'Copy the inactive rows to the destination worksheet
      For Each Cell In Rng.Columns(3)
        If Cell = "I" Then
           If DelRng Is Nothing Then Set DelRng = Cell
           Set DelRng = Union(Cell, DelRng)
           DstWks.Cells(R, "A").Resize(1, 15).Value = Cell.EntireRow.Value
           R = R + 1
        End If
      Next Cell
    
   'Delete the inactive rows on the source worksheet
    If Not DelRng Is Nothing Then DelRng.EntireRow.Delete
    
End Sub

Hi ironman
Here's another approach using autofilter

Option Explicit

Sub Test()
    Dim LRs1 As Long
    Dim LRs2 As Long
    LRs2 = Sheets("Inactive").Range("C" & Rows.Count).End(xlUp).Row

    With Sheets("Active")
        .UsedRange.AutoFilter Field:=3, Criteria1:="I"
        LRs1 = Sheets("Active").Range("C" & .Rows.Count).End(xlUp).Row
        .Range("A3:O" & LRs1).Copy Destination:=Sheets("Inactive").Range("A" & LRs2 + 1)
        .Range("A3:O" & LRs1).EntireRow.Delete
        .AutoFilterMode = False

    End With
End Sub

John

Try this:

Option Explicit

Sub ArchiveInactive()
'JBeaucaire     (2/3/2010)
'Move "inactive" clients to another worksheet
Dim LR As Long, NR As Long, wsI As Worksheet

Set wsI = Sheets("Sheet2")              'TARGET sheet
    
If ActiveSheet.Name = wsI.Name Then     'make sure the SOURCE sheet is active
    MsgBox "Start macro from data sheet"
    Exit Sub
End If

NR = wsI.Range("A" & Rows.Count).End(xlUp).Row + 1
If NR = 2 Then Rows("1:1").Copy wsI.Range("A1") 'add titles if needed

Columns("C:C").AutoFilter
Columns("C:C").AutoFilter Field:=1, Criteria1:="i"

LR = Range("C" & Rows.Count).End(xlUp).Row
If LR > 1 Then      'verify there is data to transfer, then do it
    Range("A2:O" & LR).SpecialCells(xlCellTypeVisible).Copy wsI.Range("A" & NR)
    Range("A2:O" & LR).SpecialCells(xlCellTypeVisible).Delete xlShiftUp
End If

ActiveSheet.AutoFilterMode = False
End Sub

Good work. It's gratifying to solve issues by ones' self.
John