I created a code to pass data from one excel file to another (it is a little confusing project because I am in a company), I believe that the code is correct in general but when I run the program it gives an error called "Object Required" in the variable " k ", can someone help me?
Module (Public Variables):
WorkBook Open:
Principal Code:
Module (Public Variables):
VBA Code:
Option Explicit
Public myRecentFile As String
Public counter, counter2 As Integer, res_1, res_2
Public sh As Worksheet, ch As Worksheet
Public k As LongWorkBook Open:
Code:
Private Sub Workbook_Open()
Call recentFilesSpecificFolder
End SubPrincipal Code:
Code:
Public Sub WriteCells()
res_1 = 0
res_2 = 0
ws = Workbooks(myRecentFile).Sheets.Count
ws2 = Workbooks("EEC QEC.xlsm").Sheets.Count
k = ws2.Cells(Rows.Count, "H").End(xlUp).Offset(1).Row <--------------------- Error is there!!!!!
For counter = 1 To ws
For counter2 = 1 To ws2
Set ch = Workbooks(myRecentFile).Worksheets(counter)
Set sh = Workbooks("EEC QEC.xlsm").Worksheets(counter2)
If ch.Name = "QEC 12 IF" Or ch.Name = "QEC 22 IF" Or ch.Name = "QEC 24 IF" Or ch.Name = "QEC 41 IF" Or ch.Name = "QEC 42 IF" Or ch.Name = "QEC 43 IF" Or ch.Name = "QEC 44 IF" Then
If sh.Name = "QEC 1.2 - montagem" Or sh.Name = "QEC 2.2 -SALA LIMPA" Or sh.Name = "QEC 2.4 Logística" Or sh.Name = "QEC 4.1 - MONTAGEM MANUAL(past)" Or sh.Name = "QEC 4.2 - Desmoldagem" Or sh.Name = "QEC 4,3 - RTM" Or sh.Name = "QEC 4,4 - HOT DRAPE" Then
If Range("H" & k).Value = "" Then
ch.Range("W110").Copy _
sh.Range("H" & k)
ch.Range("AI110").Copy _
sh.Range("I" & k).Offset(0, 1)
Else
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1).Select
res_1 = res_1 + 1
Loop
ch.Range("W110").Copy _
sh.Range("H" & (k + res_1))
ch.Range("AI110").Copy _
sh.Range("I" & (k + res_1)).Offset(0, 1)
res_1 = Empty
End If
End If
ElseIf ch.Name = "QEC 11 IF" Or ch.Name = "QEC 21 IF" Or ch.Name = "QEC 23 IF" Or ch.Name = "QEC 25 IF" Or ch.Name = "QEC 26 IF" Or ch.Name = "QEC 27 IF" Or ch.Name = "QEC 28 IA" Or ch.Name = "QEC 31 IF" Then
If sh.Name = "QEC 11 IF- Pintura" Or sh.Name = "QEC 2,1 Corredor" Or sh.Name = "QEC 23 IF- SALA DE CORTE" Or sh.Name = "QEC 2.5 Acabamento" Or sh.Name = "QEC 2,6 - Ultra sons" Or sh.Name = "QEC 2,7 - flow" Or sh.Name = "QEC 2.8 - Gabinetes" Or sh.Name = "QEC 3,1- Autoclave" Then
If Range("D" & k).Value = "" Then
Workbooks("EEC QEC.xlsm").Range("D" & k).Value = ch.Range("W110").Value + ch.Range("AI110").Value
Else
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1).Select
res_2 = res_2 + 1
Loop
Workbooks("EEC QEC.xlsm").Range("D" & (k + res_2)).Value = ch.Range("W110").Value + ch.Range("AI110").Value
res_2 = Empty
End If
End If
End If
res_1 = Empty
res_2 = Empty
Next counter2
Next counter
counter = Empty
Call Fill_2021
End Sub
Private Sub Fill_2021()
Workbooks("EEC QEC.xlsm").Worksheets("2021").Select
For counter = 1 To ws
Set sh = Workbooks("EEC QEC.xlsm").Worksheets(counter)
If Range("B" & k).Value = "" Then
If sh.Name = "QEC 1.2 - montagem" Or sh.Name = "QEC 2.2 -SALA LIMPA" Or sh.Name = "QEC 2.4 Logística" Or sh.Name = "QEC 4.1 - MONTAGEM MANUAL(past)" Or sh.Name = "QEC 4.2 - Desmoldagem" Or sh.Name = "QEC 4,3 - RTM" Or sh.Name = "QEC 4,4 - HOT DRAPE" Then
Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("H" & k).Value
ActiveCell.Offset(0, 1).Select
Else
Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("D" & k).Value
ActiveCell.Offset(0, 1).Select
End If
Else
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1).Select
Loop
If sh.Name = "QEC 1.2 - montagem" Or sh.Name = "QEC 2.2 -SALA LIMPA" Or sh.Name = "QEC 2.4 Logística" Or sh.Name = "QEC 4.1 - MONTAGEM MANUAL(past)" Or sh.Name = "QEC 4.2 - Desmoldagem" Or sh.Name = "QEC 4,3 - RTM" Or sh.Name = "QEC 4,4 - HOT DRAPE" Then
Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("H" & k).Value
ActiveCell.Offset(0, 1).Select
Else
Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("D" & k).Value
ActiveCell.Offset(0, 1).Select
End If
End If
Exit For
Next counter
counter = Empty
Call Fill_Ilumination
End Sub
Private Sub Fill_Ilumination()
Workbooks("EEC QEC.xlsm").Worksheets("Iluminação Exterior").Select
For counter = 1 To ws
Set sh = Workbooks("EEC QEC.xlsm").Worksheets(counter)
If Range("N" & k).Value = "" Then
If sh.Name = "QEC 1.2 - montagem" Or sh.Name = "QEC 2.2 -SALA LIMPA" Or sh.Name = "QEC 2.4 Logística" Or sh.Name = "QEC 4.1 - MONTAGEM MANUAL(past)" Or sh.Name = "QEC 4.2 - Desmoldagem" Or sh.Name = "QEC 4,3 - RTM" Or sh.Name = "QEC 4,4 - HOT DRAPE" Then
Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("H" & k).Value
ActiveCell.Offset(0, 1).Select
Else
Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("D" & k).Value
ActiveCell.Offset(0, 1).Select
End If
Else
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1).Select
Loop
If sh.Name = "QEC 1.2 - montagem" Or sh.Name = "QEC 2.2 -SALA LIMPA" Or sh.Name = "QEC 2.4 Logística" Or sh.Name = "QEC 4.1 - MONTAGEM MANUAL(past)" Or sh.Name = "QEC 4.2 - Desmoldagem" Or sh.Name = "QEC 4,3 - RTM" Or sh.Name = "QEC 4,4 - HOT DRAPE" Then
Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("H" & k).Value
ActiveCell.Offset(0, 1).Select
Else
Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("D" & k).Value
ActiveCell.Offset(0, 1).Select
End If
End If
Next counter
counter = Empty
End Sub
Public Sub recentFilesSpecificFolder()
Dim myFile As String, fileExtension As String, recentDate As Date
myDirectory = Environ("userprofile") & "\Documents\Projeto_Luis\Andre\EEC\QE"
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
MsgBox "Path: " & myDirectory & vbCrLf & "File: " & myMostRecentFile
Call WriteCells
End Sub
