Macro stops after the first row , automation error

Question

I was writing a macro and when I run the program, it runs fine when reading the first row but when it loops around and does the second row, I get an error, saids automation problem and the macro quits. I was wondering what's going on that it works fine for the first loop but not the second.

Basically, what I want the macro to do it read rows 8 - 25, if the cell has a date in cell (i) (i being 8, 9, 10, etc..), column B then copy row and paste it to another workbook.

Any body have any ideas? thanks! :)

Sub Update()

Dim Request As Workbook
Dim blank As Worksheet
Dim oakfield As Workbook

Set Request = Workbooks("Request_Microbiological_Analysis(blank).xlsm")
Set blank = Request.Worksheets("blank")
Set oakfield = Workbooks.Open("O:\_Public\Quality_Oakfield.xlsm")


With ThisWorkbook

Dim i As Long

For i = 8 To 25

If IsDate(Cells(i, 2)) Then

blank.Cells(i, "A").Resize(, 12).Copy

oakfield.Worksheets("Microlog").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial xlPasteValuesAndNumberFormats

ActiveWorkbook.Save

ActiveWorkbook.Close


ElseIf IsEmpty(Cells(i, 2)) Then

MsgBox "Oakfield Quality Updated"

End If

Next i

End With

MsgBox "Quality System Updated"



End Sub

Answer 1

Your issue here appears to be due to the fact that you close ActiveWorkbook in your loop, but then don't open it again. An automation error typically occurs when a workbook object is being referenced when it isn't open. You need to wait until after the loop to close your workbook.