TMP file not found error when creating multiple workbooks in a loop

[rjclark]

I have ran this on 2 other computers now and get mixed results. One does not error, the other errors some, but less than mine. I think this is some type of resource or priority issue. Can anyone help with this?

[J FelixBosco]

Check your pc processes or schedule tasks for any scheduled temp cleaners.

[rjclark]

Temp files are cleared. I found this and it looks to be a similar problem that was called out as a bug:
https://www.excelbanter.com/excel-di...vb***-tmp.html
Since this is by no means a quick fix. Does anyone have another method to copy a sheet into a new workbook.
BTW, did you try the sample loop and did it work for you?

[SamT]

Does anyone have another method to copy a sheet into a new workbook.

Sub SamT_TestLoopSample()
Dim i As Long
Dim NewBk As Object
FPath = "Define" & PathSeparator

Application.DisplayAlerts = False

For i = 1 To 20
   FName = "Test" & i & ".xlsx"

   ThisWorkbook.Sheets("CopyMe").Move
   Set NewBk = ActiveWorkbook
   NewBk.SaveAs Filename:=FPath & FName
   NewBk.Close
Next i

Application.DisplayAlerts = True
End Sub

[rjclark]

Thank you SamT. The provided code doesn't copy the sheet, it moves it.

[SamT]

Simply change ".Move" to ".Copy"

[rjclark]

I have solved this by permanently moving the sheet I'm exporting into a template file, opening it, populating it, and saving it as the new file. This avoids the bug that I can't seem to get around. Thanks to all for the support.

[J FelixBosco]

try running excel.exe as administrator. Right click -> run as administrator from the previous location.