Hi all
I have a macro that I couldn't figure out how to fix it. Bottom code is a part of a longer macro.
When I run the full macro, my excel will just hang and when I test run line by line, I found the below line is looping forever causing it to hang. However, when i copied that part of code on its standalone module, it will complete within seconds without hang. Why is that ?
Appreciate any inputs, thank you
I have a macro that I couldn't figure out how to fix it. Bottom code is a part of a longer macro.
VBA Code:
Sub test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Source file path (wb)
Source_path = Sheets("Source").Range("B4").Value
Alternative_sourcepath = Sheets("Source").Range("B5").Value
Source_filename = Sheets("Source").Range("A18").Value
'Open Source file
Dim wb As Workbook
Dim wb1 As Workbook
On Error Resume Next
Set wb = Workbooks.Open(Source_path & Source_filename, False, True)
If wb Is Nothing Then
Set wb1 = Workbooks.Open(Alternative_sourcepath & Source_filename, False, True)
Application.DisplayAlerts = True
End If
'Copy IRO Daily PV01 EXP & %
On Error Resume Next
wb.Sheets("FXO_IRD_IRO_PV01_BD1_EXT1").Activate
If wb Is Nothing Then
wb1.Sheets("FXO_IRD_IRO_PV01_BD1_EXT1").Activate
End If
ActiveWorkbook.Sheets("FXO_IRD_IRO_PV01_BD1_EXT1").Activate
Dim i7 As Long, nr7 As Long
nr7 = ThisWorkbook.Sheets("FXD_NL_PV01").Range("CO" & Rows.Count).End(xlUp).Row + 1
lastrow7 = Cells(Rows.Count, 1).End(xlUp).Row
If nr7 < 6 Then nr7 = 6
With ActiveWorkbook.Sheets("FXO_IRD_IRO_PV01_BD1_EXT1").Range("C3:AF" & lastrow7)
For i7 = 1 To .Columns.Count Step 2
.Columns(i7).Resize(, 2).Copy Destination:=ThisWorkbook.Sheets("FXD_NL_PV01").Cells(nr7, (i7 + 61) * 3 / 2)
Next i7
End With
When I run the full macro, my excel will just hang and when I test run line by line, I found the below line is looping forever causing it to hang. However, when i copied that part of code on its standalone module, it will complete within seconds without hang. Why is that ?
Appreciate any inputs, thank you
VBA Code:
With ActiveWorkbook.Sheets("FXO_IRD_IRO_PV01_BD1_EXT1").Range("C3:AF" & lastrow7)
For i7 = 1 To .Columns.Count Step 2
.Columns(i7).Resize(, 2).Copy Destination:=ThisWorkbook.Sheets("FXD_NL_PV01").Cells(nr7, (i7 + 61) * 3 / 2)
Next i7