Hello,
I currently have a spread sheet where the user enters 'y' in the G column. If a y is entered, the user can then press the 'Update' button, and the rows containing a y in the g column will be moved to a different sheet, 'History'.
It works fine if there are y's entered into the column, but I want to be able to avoid the user getting: "Run-Time Error '1004': no cells were found" when no y's are entered into the g column.
The current code is this:
Private Sub UpdateHistory2_Click()
Application.ScreenUpdating = False
Dim ws As Worksheet, ms As Worksheet
Set ws = Sheets("Current Contractors")
Set ms = Sheets("History Contractors")
ws.ListObjects("Table1").Range.AutoFilter
With ms
'heading:
.Range(.Cells(1, 1), .Cells(1, 8)).Value = Array("Today's Date", _
"Purchase Order Date", _
"Contractor", _
"Equipment", _
"Description", _
"Expected Time of Completion", _
"Completed? y or n", _
"Time of Completion")
End With
With ws.Range("A1:H" & ws.Cells.Find("*", , , , xlByRows, xlPrevious).Row)
.AutoFilter 7, "y"
.Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Copy
ms.Range("A" & ms.Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
.Resize(.Rows.Count - 1).Offset(1).EntireRow.Delete Shift:=xlUp
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub
I have attached my work book. The following code is for Current Contractors and History Contractors, although almost the exact code is being used for Maintenance Sheets.
Please Help
Bookmarks