How to archive rows from one worksheet to another, based on the value of a cell in each row
Category: [Excel] Demo Available
How to archive rows from one worksheet to another, based on the value of a cell in each row
Requirement:
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.
Solution:
Code:
Sub ArchiveInvoces()
With Worksheets("DRS REGISTER").ListObjects("Table1")
.Range.AutoFilter Field:=11, Criteria1:="YES"
On Error GoTo NoRows
With .DataBodyRange.SpecialCells(xlCellTypeVisible)
Worksheets("ARCHIVE").ListObjects("Table14").ListRows.Add
.Copy Worksheets("ARCHIVE").ListObjects("Table14").ListColumns(1).Range(Worksheets("ARCHIVE").ListObjects("Table14").ListRows.Count + 1)
.EntireRow.Delete
End With
.Range.AutoFilter Field:=11
End With
NoRows:
End Sub
OR
Code:
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
tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
dws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
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.