Code works only half of the time, FILE NOT FOUND error & Run - time error

logandiana

Board Regular
Joined
Feb 21, 2017
Messages
67
I've created a workbook similar to many that I've created in the past.
For some reason, the code in this workbook will stop at the same spot about 50% of the time.
Here's an abbreviated portion of the code that includes issue:

VBA Code:
Dim RFID As Workbook
Dim exp, rep3, rep4, rep7, rep5, rep6, rep8, CP, WIN As Worksheet
Dim LR, LR1 As Long
Dim rgDT, rgCR, rgOP As Range
Dim shp As Shape
Dim ArrayOne, ArrayTwo As Variant

Sub DoStuff()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set RFID = ThisWorkbook
Set exp = RFID.Sheets("export")
Set rep3 = RFID.Sheets("3000")
Set rep4 = RFID.Sheets("4000")
Set rep7 = RFID.Sheets("7000")
Set rep5 = RFID.Sheets("5000")
Set rep6 = RFID.Sheets("6000")
Set rep8 = RFID.Sheets("8000")
Set CP = RFID.Sheets("CAMs progress")
Set WIN = RFID.Sheets("week in numbers")
...

ArrayOne = Array("export", "3000", "4000", "5000", "6000", "7000", "8000")
ArrayTwo = Array("week in numbers", "CAMs progress")

RFID.Sheets(ArrayOne).Copy

ActiveWorkbook.SaveAs Filename:=RFID.Path & "\completed\" & "RFID Status " & Format(Now(), "YYYY-MM-DD  hh.mm AMPM") & " " & ".xlsx", FileFormat:=xlOpenXMLWorkbook
RFID.Activate

RFID.Sheets(ArrayTwo).Copy

ActiveWorkbook.SaveAs Filename:=RFID.Path & "\completed\" & "RFID CAM and Region " & Format(Now(), "YYYY-MM-DD  hh.mm AMPM") & " " & ".xlsx", FileFormat:=xlOpenXMLWorkbook
...
When the code gets to RFID.Sheets(ArrayOne).Copy I get a Microsoft message that says File not found: C:\Users\user\Appdata\Local\Temp\VB7284.tmp
If I click on that then it moves to a VBA run time error Method 'Copy' of object 'Sheets' failed and is highlighted on the above mentioned RFID.Sheets(ArrayOne).Copy

This is my first time using, the sheets in a named array. Previously I've always just done Sheets(Array("","","")).copy ,but I was getting the same errors that way as well.
I guess I could understand if this failed every time, but the fact that it works about half of the time really has me puzzled. Is my file corrupt or something?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
It is possible that some of your Sheets have a problem, perhaps with some formula that refers to some other book.
I did the test in a new book with the names of the sheets and it works for me.

Try to test the code in a new book with some data on each sheet. Just to rule out the macro problem and focus on the data problem of the sheets.
 

logandiana

Board Regular
Joined
Feb 21, 2017
Messages
67
I've created a new workbook with everything new. Then I just copied the code over. It seems that nothing has changed with the issue I am having though. I don't have any formulas in any of the sheets that are being copied, it's just unformatted numbers and text.
It's still really weird that it only happens some of the time.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
Sorry I can't help, I did several tests and they all work.
Maybe some of your excel version, if you could reinstall it.
 

Forum statistics

Threads
1,078,283
Messages
5,339,281
Members
399,291
Latest member
Bdbd55

Some videos you may like

This Week's Hot Topics

Top