How to archive rows from one worksheet to another, based on the value of a cell in each row

< Back to Search results

 Category: [Excel]  Demo Available 

How to archive rows from one worksheet to another, based on the value of a cell in each row



The user has a worksheet that I would like to move the contents of a row to another sheet based on the cell content in column 'K' = "yes", effectively archiving any jobs that have been invoiced.


A VBA code that the user can attach to a form control button would be ideal.

A sample spreadsheet has been attached so any help offered would be appreciated.





Sub ArchiveInvoces()
  With Worksheets("DRS REGISTER").ListObjects("Table1")
    .Range.AutoFilter Field:=11, Criteria1:="YES"
    On Error GoTo NoRows
    With .DataBodyRange.SpecialCells(xlCellTypeVisible)
      .Copy Worksheets("ARCHIVE").ListObjects("Table14").ListColumns(1).Range(Worksheets("ARCHIVE").ListObjects("Table14").ListRows.Count + 1)
    End With
    .Range.AutoFilter Field:=11
  End With
End Sub




Sub CopyToArchive()
Dim sws As Worksheet, dws As Worksheet
Dim tbl As ListObject

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set sws = Sheets("DRS REGISTER")
Set tbl = sws.ListObjects(1)
Set dws = Sheets("ARCHIVE")
sws.AutoFilterMode = False

With tbl.Range
    .AutoFilter field:=11, Criteria1:="YES"
    If tbl.Range.Columns(11).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        dws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
    End If
    tbl.Range.AutoFilter field:=11
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Click the button called "Copy To Archive " on DRS Register Sheet to run the code.


Obtained from the OzGrid Help Forum.

Solution provided by JonathanVH and sktneer.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to delete rows with no value
How to find a value in a sheet and give back related data to another sheet
How to auto copy data from master list to sub worksheets based on data value in one column
How to use VBA code to transpose any copy values X number of times


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)