New
#1
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:
While in my second macro that works fine: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 '''
Any idea on what causes this and how to remedy?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 '''
Thanks,
Peatawn