How do I avoid subscript out range error 9 when copying workbook with dynamic names?

Question

I keep getting error 9 when I try to import a sheet from another workbook to my current workbook.

This is my code. It keeps highlighting the last row in yellow. I'm trying to copy a sheet called sheet1 from a workbook called MB_OP Report_Today'sDate to the workbook AEM_WK_current week.

Sub InQltyMB_1()
    ' InQlyMB Macro
    ' Imports the MB sheet
     For WeekNum = 40 To 2500
        szToday = Format(Date, "MM.D.YYYY")
        Windows("MB_OP Report_" & szToday & ".XLS").Activate
        Sheets("Sheet1").Select
        Sheets("Sheet1").Copy Before:=Workbooks("AEM_WK_" & WeekNum & ".xlsx").Sheets(1)
    Next WeekNum
End Sub

OP - needing more context here. I hope the plan isn't to open 2460 workbooks and then loop through them all. You should declare some workbook variables and set accordingly so you can explicitly refer to your objects. — urdearboy, Nov 18 '20 at 16:19
Also read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Nov 18 '20 at 16:23
I'm new to VBA so I'm lost a bit hahaha. I did the loop because every week, the workbook name's increases by one. So this week it's AEM_WK40 and next week it's AEM_WK41 etc. I'm not sure how to do the loop so I put 2500 lol. What should I do? — Romio Rodriguez, Nov 18 '20 at 16:32
How do you know the week - is it based on `szToday`? If so, just open the one workbook. — SJR, Nov 18 '20 at 16:36
No i change the week number manually but to call the file, I want to make a loop that increases by one in order to select the file dynamically — Romio Rodriguez, Nov 18 '20 at 16:44
That doesn't make a lot of sense to me. Why go through a loop if you already know the value? If the week number can't be worked out store it in a cell and increase that by 1 each time. — SJR, Nov 18 '20 at 16:51
If there is a systematic way to refer to the file then you should 100% do that. The loop is just going to open you up to potential errors (*this method would require only the correct workbook being open, if you have an older one open you will grab the wrong sheet*) and it is also just going to make your code less efficient since you will perform 2459 useless checks just for 1 to match — urdearboy, Nov 18 '20 at 17:00

score 0 · Answer 1 · answered Nov 18 '20 at 21:22

Create variables for your attribuitions, avoid the use of Activate, Select and events in general.

In order to work I just created workbooks variables and remove the activate logic

    Sub InQltyMB_1()
      '
      ' InQlyMB Macro
      ' Imports the MB sheet
    Dim wbReport As Workbook
    Dim wbAEM_WK As Workbook
    
    For WeekNum = 40 To 2500
        
        szToday = Format(Date, "MM.D.YYYY")
        
        Set wbReport = Workbooks("MB_OP Report_" & szToday & ".XLS")
         
        Set wbAEM_WK = Workbooks("AEM_WK_" & WeekNum & ".xlsx")
         
        wbReport.Sheets(1).Copy Before:=wbAEM_WK.Sheets(1)
        
        'Maybe if you won't use the workbooks you could close them, uncomment bellow rows for close
        'wbReport.Close
        'wbAEM_WK.Close
        
    Next WeekNum
    
End Sub

How do I avoid subscript out range error 9 when copying workbook with dynamic names?

1 Answers1