Part of Code loops forever but completes within seconds in its own module

[bunny1122]

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.

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

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

 

[Alex Blakenburg]

Your On Error Resume Next statements are masking when your code is erroring out and it is not finding either of your source workbooks.
It then continues looping in the For loop with i7 not incrementing but just showing 0 on each loop.

 

[bunny1122]

Thanks Alex! I fixed it and now it works fine.

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.