I'm having some issues using DIR(), the code below (which I found on this forum ) worked fine when I first set it up and ran
it. I was able to run it multiple times with it opening and copying the information wanted. The issue is that once I close
Excel and reopened the file, the code will no longer work and it's giving me a run-time error 1004: application-defined or
object-defined error when trying to execute the line of code Workbooks.Open wbName (or atleast that when it stop executing
but could it be the line above?). If I open another workbook and add this code, it will again work until I
save/close/reopen. As you can probably tell, I'm by far no expert in VBA, I'm thinking that wbName is being stored with the
file path and file name and it's not clearing out when I close and reopen Excel?
Dim DeptFolder As String
Dim MyPath As String
Dim wbName As String
MyPath = "FinancialGeneral Ledger, Revenue, Forecast filesCash Flash"
DeptFolder = "DCF_Data_Files"
wbName = Dir(MyPath & DeptFolder & "" & "2012 HEI Forecast*.xlsx")
Do While Len(wbName) > 0
wbName = Dir
Set WkbkTemp = ActiveWorkbook
Sheets("ACTUAL VS. FORECAST").Select
With Cells(1, 1)
Selection.MergeCells = False
Application.CutCopyMode = False