I am getting error 424 "Object Required" at the end of my code on the line where it says "Var_Path.Activate". Basically what I want it to do is make that workbook active. Can anyone help?
Thanks
Thanks
Code:
Sub Transfer_Var_to_Log()
'Define Variables
Dim Start_Row As Integer 'This variable determines what row to begin at for pasting information into the Log
Dim Var_Path As Variant 'This variable is the user selected path to the Var Log(s)
Dim Log_Path As Variant 'This variable is the user selected path to the Leaf Log
Dim Quantity_Line_Items As Integer 'This variable is the number of line items for each VAR that are going to be copied and pasted into the LOG
'Have the user select the VAR(s)
Var_Path = Application.GetOpenFilename(Title:="Select the VAR(s) you wish to transfer data from", MultiSelect:=True)
' If Var_Path = CStr(False) Then
' MsgBox ("You have not selected a VAR file")
' Exit Sub
' End If
'Have the user select the Log path
Log_Path = "\\usn002\public\MacClymont\Nissan_Leaf\Leaf_Log.xlsx"
If Log_Path = CStr(False) Then
MsgBox ("You have not selected a Log file")
Exit Sub
End If
'First, identify the first empty line in the leaf_log so we know where to begin pasting information
Set Log_Path = Workbooks.Open(Log_Path)
For Start_Row = 5 To 4000
If Cells(Start_Row, "A") = "" And Cells(Start_Row, "B") = "" Then
If Cells(Start_Row + 1, "A") = "" And Cells(Start_Row + 2, "A") = "" And Cells(Start_Row + 3, "A") = "" And Cells(Start_Row + 4, "A") = "" And Cells(Start_Row + 5, "A") = "" Then
Exit For
End If
End If
Next Start_Row
'Open the VAR file(s) one by one
For fnum = LBound(Var_Path) To UBound(Var_Path)
Workbooks.Open Filename:=Var_Path(fnum)
'Find out how many line items we have for this particular VAR
For Quantity_Line_Items = 6 To 100
If Cells(Quantity_Line_Items, "A") = "" Then
Quantity_Line_Items = Quantity_Line_Items - 6
Exit For
End If
Next Quantity_Line_Items
'Start transfering information one line at a time
For i = 1 To Quantity_Line_Items
Var_Path.Activate
Range(Cells(i + 5, "A"), Cells(i + 5, "P")).Copy
Log_Path.Activate
Cells(Start_Row - 1 + i, "A").Paste
Next i
Next fnum
End Sub