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