File unable to open

  • Thread starter Thread starter Len
  • Start date Start date
L

Len

Hi,

Something has gone wrong with excel vba codes below that unable to
open excel files from a list index in sheet(Main)

Extract of vba codes

Sheets("Main").Select
FileList = Cells(Rows.Count, 1).End(xlUp).Row
For Each Cell In Cells(1, 1).Resize(FileList, 1)
Filenm1 = Cell.Value

On Error Resume Next

With Application.FileSearch
.NewSearch

.LookIn = "J:\BBT\LO\Budget\Budget Actual\Acad"
.FileType = msoFileTypeExcelWorkbooks

.Filename = Filenm1 & "*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through
all

Workbooks.Open (.FoundFiles(lCount))

ActiveWorkbook.Activate
Sheets("P+L").Select
Dim i As Long
Dim Lstrow As Long
Lstrow = Cells(Rows.Count, "A").End(xlUp).Row
If Lstrow > 0 Then
For i = 5 To Lstrow
If Cells(i, 1).Value <> "" Then
Cells(i, 1).Copy
Cells(i, 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Cells(i, 1).ClearContents
End If
Next
Else
MsgBox "It appears that the file is empty, check the file again"
End If

Any helps will be appreciated as I'm excel vba beginner

Thanks in advance

Regards
Len
 
Back
Top