CODEWITHSUNDEEP
vbaexcel
Aysee
Aysee

Reputation: 45

My VBA code makes Excel crash every time I run it

Does anyone know what line or lines of code keep making my Excel crash every time I run the macro? When I comment out the copying from a different workbook and pasting into the current workbook, it runs fine, so I'm guessing it has something to do with those lines but I don't see why copying and pasting would cause it to crash...

The code is supposed to go through all the files in a folder that are specifically the .xlsb file type and copy a selection from that .xlsb file and paste it into the current .xlsm file.

Option Explicit

Sub OpenFiles()

    Dim objFs As Object
    Dim objFolder As Object
    Dim file As Object

    Set objFs = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFs.GetFolder(Application.ThisWorkbook.Path)

    Dim lastCol As Integer
    lastCol = 2

    For Each file In objFolder.Files
        If file Like "*.xlsb" Then

            Dim src As Workbook
            Set src = Workbooks.Open(file.Path, True, True)

            src.Worksheets("Rates").Range("C5", "C29").Copy
            ThisWorkbook.Worksheets("Sheet4").Cells(3, lastCol).PasteSpecial xlPasteValues

            src.Close False
            Set src = Nothing
            lastCol = lastCol + 1
        End If
    Next
End Sub

Upvotes: 3

Views: 1482

Answers (1)

user4039065
user4039065

Reputation:

Take the Dim out of the loop, check to ensure you're not trying to reopen ThisWorkbook and transfer values instead of copying with the clipboard.

...
Dim src As Workbook

For Each file In objFolder.Files
    If file Like "*.xlsb" and file not like ThisWorkbook.name Then

        Set src = Workbooks.Open(file.Path, UpdateLinks:=True, readonly:=True)

        with src.Worksheets("Rates").Range("C5:C29")
            ThisWorkbook.Worksheets("Sheet4").Cells(3, lastCol).resize(.rows.count, .columns.count) = .value
        end with

        src.Close False
        Set src = Nothing
        lastCol = lastCol + 1
    End If
Next file
...

Upvotes: 3

Related Questions