[VBA] Automation Error - no context, no error code, nothing.


Active Member
Aug 1, 2018
Hi all,

Workflow below

  1. ALL Code resides in sheet "Super Automation"
  2. Information is gathered on "Ads" tab and brought into "adstemp"
  3. Advert Data is opened and relevant tours gathered
  4. Advert Data closed, tourlist is loaded into Automation Hub
  5. Several spreadsheets are now opened (Regional Press Report, Price Panels, Advert Data again) and relevant information is extracted, sheets are then closed
  6. Points are allocated in Automation Hub and suitable tours are ranked
  7. This then gets placed back into Ads, Automation Hub is closed, tours are then committed to the Advert Data - opened in Read Only
  8. If there are existing lines in Advert Data, they are scrubbed out and new ones are written in place
  9. Advert Data saves and closes, Super Automation saves and drops down a line, the process repeats

In red is where I think this process is going pear-shaped. I am getting a popup on screen that says "Automation Error" - that's it. There's no error code or any other message. When I click ok, everything closes and re-opens with a recovered version.

What I am noticing is that it nearly always re-opens in the same place, sometime after step 5, because it has found tours to put in Automation Hub, but not ranked them yet, or if it has ranked them, it hasn't brought them back into Adstemp.

Alternatively, sometimes it will break without any error code popping up, and in this instance, tours appear to be in Automation Hub, ranked, but not brought back to the temp tab.

Here is a snippet of the code:

On Error GoTo NoSave

Range("1:1").Delete xlUp

If rop = True Then
newdir = "H:\Sales\Regional Press Selections\" & wbyr & "\Automation Files\ROP\wc " & Format(DateValue(WCD), "yyyy-mm-dd")
newdir = "H:\Sales\Regional Press Selections\" & wbyr & "\Automation Files\RT\wc " & Format(DateValue(WCD), "yyyy-mm-dd")
End If

If Dir(newdir, vbDirectory) = vbNullString Then
On Error Resume Next
MkDir newdir
On Error GoTo 0
End If

'nsname = Replace(PaperName, "/", "") & " - " & PaperName !@!

nsname = Replace(PaperName, "/", "&")

Set n = ActiveWorkbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
n.SaveAs filename:=newdir & "\" & nsname & " - Super - " & ".csv", FileFormat:=xlCSV, CreateBackup:=False
n.Close True
Application.DisplayAlerts = True
Application.ScreenUpdating = False

Resume Skip
' Bring results back to adstemp
Range("E3:E" & Lastrow).Copy adstemp.Range("H12")
Range("F3:F" & Lastrow).Copy adstemp.Range("I12")
Range("K3:K" & Lastrow).Copy adstemp.Range("J12")
Range("W3:W" & Lastrow).Copy adstemp.Range("K12")
Range("V3:V" & Lastrow).Copy adstemp.Range("L12")
Range("Y3:Y" & Lastrow).Copy adstemp.Range("M12")

Application.EnableEvents = True
Application.DisplayAlerts = False

If ah.ReadOnly = False Then
ah.Close True
ah.Close False
End If

In here, what happens is the results of the Automation Hub's start page (sp) are copied out and saved to the given directory, that's all closed, then back on the Automation Hub it copies the results of the automation back to adstemp.

I think it's here that something isn't going right, but I don't know what and Excel isn't being very verbose about it.

Hoping someone can help. This loop is >600 iterations and so far it's broken 14 times - not good for an automated process to replace a manual one!


Well-known Member
Apr 28, 2004
Office Version
What happens if you remove/comment out all the 'On Error...' stuff?


Active Member
Aug 1, 2018
I guess if it couldn't save the automation file to the directory specified it would break the code? I can give it a shot.


Active Member
Aug 1, 2018
What happens if you remove/comment out all the 'On Error...' stuff?
Just to note, this didn't solve the issue, however I'm reluctant to mark it as solved, but I stripped all the macros out of the "Automation Hub" and resaved it as an xlsx, and so far it seems to have stopped the Automation Error error (I am only on iteration 112 out of 666 though!)

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...