VBA - Issue with importing sheets from each Workbook in folder, Excel crashing

[BigShango]

Hi,

I'm having some weird issue with this code.

Application.DisplayAlerts = False


Set mb = Workbooks("Main Import Book.xlsm")
ImportPath = TextBox1.Value
Importsheet = TextBox2.Value
Counter = 1


Set FSO = CreateObject("Scripting.FileSystemObject")
Set fld = FSO.GetFolder(ImportPath)


For Each fl In fld.Files
    If fl.Name Like "*.xls*" Then
        Set wb = Workbooks.Open(fl.Path)
        For i = 1 To wb.Sheets.Count
            If wb.Sheets(i).Name Like Importsheet & "*" Then
                MsgBox "found"
                wb.Sheets(i).Cells.UnMerge
                CopySheet = wb.Sheets(i).Name
                RenameSheet = "Import - " & Counter
                wb.Sheets(i).Copy before:=mb.Sheets("Sheet1")
                mb.Sheets(CopySheet).Name = RenameSheet
                wb.Sheets(i).Cells.Copy
                mb.Sheets(RenameSheet).Cells.PasteSpecial xlPasteValues
                Counter = Counter + 1
                MsgBox "done"
            End If
        Next i
        MsgBox "done 2"
        wb.Close SaveChanges:=False
        MsgBox "done 3"
    End If
Next fl



Application.DisplayAlerts = True

It's a little strange because I need to copy a sheet over, then copy the values from the original sheet over the newly copied sheet (the original is full of references to other sheets etc). I also need to unmerge the cells because for some reason it doesn't like doing PasteValues on merged cells. While a bit messy this all seems to work fine. However it crashes after the first workbook. I have even tried doing this with just one workbook in the import folder, same issue.

I stuck the MsgBoxes in to try and catch where it hangs. It does so after "done 2" and before "done 3". I can't understand why it is hanging (requiring hard close of Excel) at closing the workbook.

Any ideas?

Also if anyone could tell me a tidier way of doing the above that'd be great.


Cheers
D

 

[BigShango]

It's definitely crashing when closing the workbooks. I've commented out that line and it runs fine, just leaving all the import workbooks open.

I've tried a few different methods. Doing set cb = workbook(blah blah) then cb.close at the end, also tried fl.close or set cb = workbooks(fl.name) then cb.close and keep getting the same results.

I can't see what is causing it.