Subscript out of range error

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
157
Office Version
  1. 2016
Platform
  1. Windows
I'm trying to pass data from one excel sheet to another, but when I run the program it gives a "Subscript out of range" error, can someone help me?



VBA Code:
Public Sub recentFilesSpecificFolder()

    counter = 14

    Dim myFile As String, fileExtension As String
    Dim myRecentFile As String, recentDate As Date

    myDirectory = Environ("userprofile") & "\Documents\Projeto_Luis\André_2\EEM\QEM"
    fileExtension = "*.xls"

    If Right(myDirectory, 1) <> "\" Then myDirectory = myDirectory & "\"

    myFile = Dir(myDirectory & fileExtension)
    If myFile <> "" Then
        myRecentFile = myFile
        recentDate = FileDateTime(myDirectory & myFile)
        Do While myFile <> ""
            If FileDateTime(myDirectory & myFile) > recentDate Then
                myRecentFile = myFile
                recentDate = FileDateTime(myDirectory & myFile)
            End If
        myFile = Dir
        Loop
    End If
    myMostRecentFile = myRecentFile
    
    k = Workbooks("EEM QEM.xlsm").Sheets("Montagem Praetor + C390").Cells(Rows.Count, "N").End(xlUp).Offset(1).Row
    
    If Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, 1).Value = "" Then
        k = k + 1

    Else
    End If
    
    Call FillSheets

End Sub


Private Sub FillSheets()
    
        'Montagem
        Workbooks("EEM QEIM.xlsm").Sheets("Montagem Praetor + C390").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 11 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Montagem Praetor + C390").Cells(k, counter).Offset(0, 1).Value = Workbooks(myMostRecentFile).Sheets("QEM 11 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Montagem Praetor + C390").Cells(k, counter).Offset(0, 2).Value = Workbooks(myMostRecentFile).Sheets("QEM 111 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Montagem Praetor + C390").Cells(k, counter).Offset(0, 3).Value = Workbooks(myMostRecentFile).Sheets("QEM 111 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Montagem Praetor + C390").Cells(k, counter).Offset(0, 4).Value = Workbooks(myMostRecentFile).Sheets("QEM 111 IF").Range("AW110").Value

        
        counter = 4
        
        'Sala Comp
         Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,3 I - Sala Comp.").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 13 I").Range("W110").Value
        
        
        'Corredor Entrada
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,5 I - Corredor entrada+wc").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 15 I").Range("W110").Value
        
        
        'Aspiração
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2,4 - Aspiração Aparas Al.").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 24 IF").Range("W110").Value
        
        
        'Corredor Cab
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,1 - Corr. Cab Pint Prim").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 31 IF").Range("W110").Value
        
        
        'Liquidos Penetrantes
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,1,3- Líquidos Penetrantes").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 313 IF").Range("W110").Value
        
        
        'Tratamento Superfícies
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,1,4- Tratamento Superfíes").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 314 IF").Range("W110").Value
        
        
        counter = 7
        
        'Shot Peenig
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,4 - Shot Peenig").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 14 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,4 - Shot Peenig").Cells(k, counter).Offset(0, 1).Value = Workbooks(myMostRecentFile).Sheets("QEM 14 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,4 - Shot Peenig").Cells(k, counter).Offset(0, 2).Value = Workbooks(myMostRecentFile).Sheets("QEM 144 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,4 - Shot Peenig").Cells(k, counter).Offset(0, 3).Value = Workbooks(myMostRecentFile).Sheets("QEM 144 IF").Range("AJ110").Value
                
        
        counter = 8
        
        'Setup LP
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,4,1 - Setup LP+TS").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 141 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,4,1 - Setup LP+TS").Cells(k, counter).Offset(0, 1).Value = Workbooks(myMostRecentFile).Sheets("QEM 141 IF").Range("AJ110").Value
        
        
        'Tridimensional
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,4,2+1,4,3 -Tridimensional").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 142 I").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,4,2+1,4,3 -Tridimensional").Cells(k, counter).Offset(0, 1).Value = Workbooks(myMostRecentFile).Sheets("QEM 143 I").Range("W110").Value
        
        
        'Gabinetes
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,1,1+3,1,2 - Gabinetes").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 311 I").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,1,1+3,1,2 - Gabinetes").Cells(k, counter).Offset(0, 1).Value = Workbooks(myMostRecentFile).Sheets("QEM 312 I").Range("W110").Value
        
        
        'Z Técnica
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,1,1+3,1,2 - Gabinetes").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 32 I").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,1,1+3,1,2 - Gabinetes").Cells(k, counter).Offset(0, 1).Value = Workbooks(myMostRecentFile).Sheets("QEM 32 I").Range("AJ110").Value
        
        
        counter = 12
        
        'Montagem E2
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,6 - Montagem E2").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 16 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,6 - Montagem E2").Cells(k, counter).Offset(0, 1).Value = Workbooks(myMostRecentFile).Sheets("QEM 16 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,6 - Montagem E2").Cells(k, counter).Offset(0, 2).Value = Workbooks(myMostRecentFile).Sheets("QEM 161 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,6 - Montagem E2").Cells(k, counter).Offset(0, 3).Value = Workbooks(myMostRecentFile).Sheets("QEM 161 IF").Range("AJ110").Value
        
        
        counter = 28
        
        'Usinagem
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 2 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, counter).Offset(0, 1).Value = Workbooks(myMostRecentFile).Sheets("QEM 2 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, counter).Offset(0, 2).Value = Workbooks(myMostRecentFile).Sheets("QEM 21 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, counter).Offset(0, 3).Value = Workbooks(myMostRecentFile).Sheets("QEM 21 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, counter).Offset(0, 4).Value = Workbooks(myMostRecentFile).Sheets("QEM 22 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, counter).Offset(0, 5).Value = Workbooks(myMostRecentFile).Sheets("QEM 22 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, counter).Offset(0, 6).Value = Workbooks(myMostRecentFile).Sheets("QEM 22 IF").Range("AW110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, counter).Offset(0, 7).Value = Workbooks(myMostRecentFile).Sheets("QEM 23 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, counter).Offset(0, 8).Value = Workbooks(myMostRecentFile).Sheets("QEM 23 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, counter).Offset(0, 9).Value = Workbooks(myMostRecentFile).Sheets("QEM 23 IF").Range("AW110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, counter).Offset(0, 10).Value = Workbooks(myMostRecentFile).Sheets("QEM 25 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, counter).Offset(0, 11).Value = Workbooks(myMostRecentFile).Sheets("QEM 25 IF").Range("AJ110").Value
        
        
        counter = 40
        
        'Iluminação Exterior
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 1.2 - Logística").Cells(k, 10).Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 1).Value = Workbooks(myMostRecentFile).Sheets("QEM 11 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 11 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 2).Value = Workbooks(myMostRecentFile).Sheets("QEM 111 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 111 IF").Range("AJ110").Value + Workbooks(myMostRecentFile).Sheets("QEM 111 IF").Range("AW110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 3).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 1.2 - Logística").Cells(k, 10).Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 4).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,3 I - Sala Comp.").Cells(k, 4).Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 5).Value = Workbooks(myMostRecentFile).Sheets("QEM 14 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 14 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 6).Value = Workbooks(myMostRecentFile).Sheets("QEM 141 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 141 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 7).Value = Workbooks(myMostRecentFile).Sheets("QEM 144 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 144 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 8).Value = Workbooks(myMostRecentFile).Sheets("QEM 15 I").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 9).Value = Workbooks(myMostRecentFile).Sheets("QEM 16 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 16 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 10).Value = Workbooks(myMostRecentFile).Sheets("QEM 161 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 161 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 11).Value = Workbooks(myMostRecentFile).Sheets("QEM 2 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 2 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 12).Value = Workbooks(myMostRecentFile).Sheets("QEM 21 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 21 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 13).Value = Workbooks(myMostRecentFile).Sheets("QEM 22 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 22 IF").Range("AJ110").Value + Workbooks(myMostRecentFile).Sheets("QEM 22 IF").Range("AW110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 14).Value = Workbooks(myMostRecentFile).Sheets("QEM 23 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 23 IF").Range("AJ110").Value + Workbooks(myMostRecentFile).Sheets("QEM 23 IF").Range("AJ110").Value + Workbooks(myMostRecentFile).Sheets("QEM 23 IF").Range("AW110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 15).Value = Workbooks(myMostRecentFile).Sheets("QEM 24 IF").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 16).Value = Workbooks(myMostRecentFile).Sheets("QEM 25 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 25 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, counter).Offset(0, 17).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,1 - Corr. Cab Pint Prim").Cells(k, 4).Value
        
        
        counter = 2
        
        '2021
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Value = Workbooks(myMostRecentFile).Sheets("QEM 11 IF").Range("W110").Value + Workbooks(myMostRecentFile).Sheets("QEM 11 IF").Range("AJ110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 1).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 111 IF").Range("W110").Value + Workbooks("EEM QEIM.xlsm").Sheets("QEM 111 IF").Range("AJ110").Value + Workbooks("EEM QEIM.xlsm").Sheets("QEM 111 IF").Range("AW110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 2).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 1.2 - Logística").Cells(k, 10).Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 3).Value = Workbooks(myMostRecentFile).Sheets("QEM 13 I").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 4).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,4 - Shot Peenig").Cells(k, 11).Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 5).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,4,1 - Setup LP+TS").Cells(k, 10).Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 6).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 1,4,2+1,4,3 -Tridimensional").Cells(k, 10).Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 7).Value = Workbooks(myMostRecentFile).Sheets("QEM 15 I").Range("W110").Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 8).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 2 Usinagem").Cells(k, 40).Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 9).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 2,4 - Aspiração Aparas Al.").Cells(k, 4).Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 10).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,1 - Corr. Cab Pint Prim").Cells(k, 4).Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 11).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,1,1+3,1,2 - Gabinetes").Cells(k, 10).Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 12).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,1,3- Líquidos Penetrantes").Cells(k, 4).Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 13).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,1,4- Tratamento Superfíes").Cells(k, 4).Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 14).Value = Workbooks("EEM QEIM.xlsm").Sheets("QEM 3,2 - Z.Técnica -1").Cells(k, 10).Value
        Workbooks("EEM QEIM.xlsm").Sheets("2021").Cells(k, counter).Offset(0, 14).Value = Workbooks("EEM QEIM.xlsm").Sheets("Ilum. ext.").Cells(k, 58).Value
                
End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
What line of the code gives that error?
sorry forgot to mention, the line "If Workbooks (" EEM QEIM.xlsm "). Sheets (" 2021 "). Cells (k, 1) .Value =" "Then

k = k + 1



Else

End If "


and if we delete the line that starts to give an error, it is "Workbooks (" EEM QEIM.xlsm "). Sheets (" Praetor + C390 Assembly "). Cells (k, counter) .Value = Workbooks (myMostRecentFile) .Sheets ( "QEM 11 IF"). Range ("W110"). Value ""
 
Upvote 0
Then that indicates the most likely problem is the workbook name. Are you sure that it shouldn't be "EEM QEM.xlsm" (not QEIM) since you have "EEM QEM.xlsm" in the line of code immediately above the problem line & that did not error.
 
Upvote 0
Then that indicates the most likely problem is the workbook name. Are you sure that it shouldn't be "EEM QEM.xlsm" (not QEIM) since you have "EEM QEM.xlsm" in the line of code immediately above the problem line & that did not error.
I am really misled, thank you very much for the answer, that was the mistake
 
Upvote 0
Then that indicates the most likely problem is the workbook name. Are you sure that it shouldn't be "EEM QEM.xlsm" (not QEIM) since you have "EEM QEM.xlsm" in the line of code immediately above the problem line & that did not error.
I changed and initialized the program and the same error keeps happening any idea of what could be more?
 
Upvote 0
If you are sure that you have the correct workbook name now and the error is still on the same line, then most likely it is that the workbook does not have a worksheet called "2021"

If the error is on a different line then please post all of your revised code and advise which is the problem line.
 
Upvote 0

Forum statistics

Threads
1,214,830
Messages
6,121,839
Members
449,051
Latest member
excelquestion515

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top