My VBA code makes Excel crash every time I run it

Question

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

Not quite sure exactly where the issue is, could be a syntax error in your "Set" statement with 'src' or somewhere else. One issue I did notice is in your "pasting" statement. You are missing a period between 'PasteSpecial' and 'xlPasteValues'. Even if that's not the issue, once you fix the other one, that will create a syntax issue for you. — Matt Gaydon
– Matt Gaydon, Commented Jun 11, 2018 at 15:22
For starters, a) you cannot select a range on an inactive worksheet, b) you don't need to Select in order to copy, c) why are you saving changes when the only change might possibly be the current selection? — user4039065
– user4039065, Commented Jun 11, 2018 at 15:24
@MattGaydon - the operation is range.pastespecial paste:=xlPasteValues — user4039065
– user4039065, Commented Jun 11, 2018 at 15:28
d) you should be checking to make sure you aren't trying to reopen the workbook you are running from if it is an .xlsb. — user4039065
– user4039065, Commented Jun 11, 2018 at 15:34

user4039065 · Accepted Answer · 2018-06-11 16:07:46Z

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 ...

Collectives™ on Stack Overflow

My VBA code makes Excel crash every time I run it

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related