Why does my VBA macro end after opening file?

Hi,

I made a macro 5 years ago that I use everyday at work to compile reports. A couple of years ago, I copied the macro and made minor modifications for a very similar project. Both worked fine until I got a new PC with Windows 7. Now, my first macro just stops (without any error message) when it reaches the line to open my report file (it opens the file but doesn't continue). However, if I add stop points (maroon bullets) on the faulty line of code and the following line, I can manually make the macro continue on. While the second macro, with the similar lines of code works just find and doesn't stop or require stop points to continue.

Here is a portion of the code of the first macro that stops:

Code:

''' some code before '''
    
''' Creates the reports
    '' Copies and pastes the data in the XLSX report file
LastCol = [F1].End(xlToRight).Column
Range(Cells(2, 1), Cells(LastRow, LastCol)).Select
Selection.Copy
xlFileName = "P:\folder\Clients\folder\Rapports\20" & CurrentYear & " - " & FullMonth & "\projet1_20" & CurrentYear & FullMonth & ".xlsx"
xlFile = "projet1_20" & CurrentYear & FullMonth & ".xlsx"
Workbooks.Open FileName:=xlFileName ' <------- ***THIS IS THE LAST LINE THE MACRO EXECUTES AND THEN STOPS. ***
Sheets("TANGUAY_IVR1").Select
If Cells(2, 1) <> "" Then
Cells(1, 1).End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Else
Cells(2, 1).Select
End If
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets(FullMonth).Select
Application.Workbooks(xlFile).Save

''' some code after '''

While in my second macro that works fine:

Code:

''' some code before '''

    '' Copies and pastes the data in the XLSX report file
Range(Cells(1, 1), Cells(LastRow, LastCol)).Select
Selection.Copy
xlFileName = "P:\folder\Clients\folder\Rapports\20" & CurrentYear & " - " & FullMonth & "\BEMECO_IVR1_20" & CurrentYear & FullMonth & ".xlsx"
xlFile = "BEMECO_IVR1_20" & CurrentYear & FullMonth & ".xlsx"
Workbooks.Open FileName:=xlFileName ' <------- ***THE MACRO DOESN'T STOP HERE LIKE THE PREVIOUS ONE, IT CONTINUES ON. ***
Sheets("BEMECO_IVR1").Select
If Cells(2, 1) <> "" Then
Cells(1, 1).End(xlDown).Select
ActiveCell.Offset(1, 0).Select
RowSelect = ActiveCell.Row
Else
Cells(2, 1).Select
RowSelect = 2
End If
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
LastRow = [A1].End(xlDown).Row
Cells(RowSelect, 1).Select

''' some code after '''

Any idea on what causes this and how to remedy?

Thanks,
Peatawn

Are you declaring variables ?
Have you declared Option Explicit ?

If not it will be impossible to trace the source of the problem.
Have you got Error capture turned on in VBEditor. ?

Hi Greyghost. Yes, I declared all my variables. The code I provided is only a portion of the entire macros. I am, however, unaware of the Option Explicit and what it does. I am also unaware of how to turn on Error Capture, but I will play around VBEditor and find out. Nonetheless, when executed, the macro doesn't return any error. It just simply stops as if it was the end of the macro.

He already did (using breakpoints). I think it's problem with which window has the focus

Hi Kaktussoft. Upon opening my file (in either macro) it automatically gains focus.

On the other hand, I have gained more info on the issue. If I am working from home and connect to my work computer via TeamViewer, now both macros have more bugs. They pass through the line of code SELECTION.COPY but nothing is actually copied and in the clipboard. So when it's time to paste, the macro generates an error. Now for some reason, this leads me to believe that it's due to using computer resources to run TeamViewer and for some reason my macro doesn't like that. I don't get it though, my previous PC (that was on XP) was far less powerful and these macros were never an issue. My new PC is Windows 7, Intel Xenon CPU E5-1620 0 @3.60GHz with 16 GB of RAM.

Thanks!

And recently I made changes to other macros, which are all part of the PERSONAL.XLSA file, and updated the file on my colleagues' PCs. I just tested them and they all jam at the same place, Windows XP and Windows 7. I even plugged in my old PC and tried, now it jams at the same place. It's not an issue of system resources. There is definitely something going on in the macro. I'm starting to think corrupted file... ?

In an attempt to determine if a corrupt file could be the cause, I created a brand new PERSONAL.XLSA and copied my macros in VB. That didn't fix the issue so I ruled out corrupted file.

Then I decided to separate my macros in different modules as to alleviate the amount of code in a single module. That didn't fix the issue so I ruled out module being too heavy.

Then I used "Step by step detailed" (not sure about the precision of my translation, but it's the F8 key) and then everything went on smoothly! The macro worked just fine!

So I tried it normally again and the issue came back. So I decided to try again executing the macro manually through the macros menu instead of my shortcut keys. And the macro worked just fine again.

Therefore the issue seems to be when I activate the macro using the shortcut keys I've set up. I searched the web to see if my shortcut keys affected anything (CTRL + SHIFT + D) and found a place listing all Excel shortcuts. I tried a different combo not used in Excel (CTRL + SHIFT + E) but that doesn't work either. So I looked for a simpler combo shortcut that isn't used in Excel (CTRL + e) and then it worked. Problem solved. Can't believe it was this stupid. And the even more surprising fact, is that my second macro that works fine was also executed with CTRL + SHIFT + D seeing as that shorcut actually activated a third macro which would analyze my file and only then direct to my first faulty macro or my second okay macro.

Lesson learned, don't make your shortcut combos too complicated because it confuses Mr. Excel here.

I'd like to thank everyone for your help with this issue. Especially GrayGhost with your elaborate replies. Very appreciated!

Nice day to everyone!