Updating Data to new WorkBook

[JimB]

I have a simple(?) spreadsheet for an automotive repair business. As an example, row A19:I19 contains vehicle data that I would like to copy and paste to a new workbook, manually, at the end of each work day. But ONLY if cells J19:K19 are empty. (Total data spans A19:M33 - Each row representing a seperate vehicle).

My challenge is to:

1)Copy each row of the active sheet that fits the criteria (columns J and K empty).
2)Automatically open a named workbook.
3)Paste the values of these rows into the named workbook, without skipping rows.

I have been able to successfully complete a similar process, but for only one row, not multiples.

Any guidance would be greatly appreciated.

Thank you

 

[Tom Urtis]

See if this does what you want. Modify for sheet tab names, destination file path, and destination workbook name.

Sub FilterCopy()
On Error GoTo e
Application.ScreenUpdating = False
Dim fRange As Range, cRange As Range
Set fRange = Range("A19:M33")
Set cRange = Range("A20:I33")
Sheets("Sheet4").AutoFilterMode = False
With fRange
.AutoFilter Field:=10, Criteria1:="=", Operator:=xlAnd
.AutoFilter Field:=11, Criteria1:="=", Operator:=xlAnd
End With
cRange.SpecialCells(xlCellTypeVisible).Copy
Workbooks.Open Filename:="C:YourFilePathYourWorkbookName.xls"
Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
ThisWorkbook.Activate
Sheets("Sheet4").AutoFilterMode = False
Application.ScreenUpdating = True
Set fRange = Nothing
Set cRange = Nothing
Exit Sub
e:
MsgBox "No blank cells in both J and K of any rows.", 64, "Nothing to copy."
Sheets("Sheet4").AutoFilterMode = False
End Sub

 

[JimB]

Sorry to say, but this did not do it. Even with empty cells (J:K), I immediately get the message box prompt: "No blank cells in both J and K of any rows.", "Nothing to copy."

I will try to tweak the script and see if I can get it to do what I need...Unless you have any other suggestions.(?)

Thanks again!

 

[Tom Urtis]

I tested this several times and it worked fine every time. I'm curious why something that works in one workbook does not work in another. If you want to email me your file I'd like to see what the reason for the failure is. tomurtis@attbi.com