Open Workbook transfer info then close workbook (2007)

[VNouBA]

Good evening,
[FONT='Calibri','sans-serif'] I have this problem and I am out of idea. I have a matching code in one of my main Workbook. This code will transfer the information based on my column A information. [/font]
[FONT='Calibri','sans-serif'][/font]
[FONT='Calibri','sans-serif'][vba][/font]
[FONT='Calibri','sans-serif']Sub Test()
MsgBox Application.WorksheetFunction.Match(Range("A5:A10000"), Worksheets("Report").Range("A5:A10000"))
MsgBox Application.WorksheetFunction.Match(Range("A5:A10000"), Worksheets("Archives").Range("A5:A10000"))
End Sub[/font]
[FONT='Calibri','sans-serif'][/vba][/font]
[FONT='Calibri','sans-serif']This function gets the matching Requisition number in the "report" and archives" sheet.[/font]
[FONT='Calibri','sans-serif'][/font]
[FONT='Calibri','sans-serif']I want to delete the second one (“Archives”) to replace it with the following but it’s giving me an error:
[vba]
MsgBox Application.WorksheetFunction.Match(Range("A5:A10000"),
Workbooks.Open Filename:= _
[FONT='Calibri','sans-serif'] "C:\file\folder\info\tech\DF\records\Work Load Report\Archives.xlsx"[/font]
[/vba]
[FONT='Calibri','sans-serif']Then I need help with the following: [/font]
[vba]
If Not Intersect(Target, Range("M:M")) Is Nothing Then
If Target.Cells.Count = 1 Then
If LCase(Target.Value) = "cancelled" Then
Msg = MsgBox("Are you sure you want to cancel this file?", vbYesNo + vbQuestion + vbSystemModal, "Excel Pre-Requisition")
If Msg = vbYes Then
'If "yes" is selected from the popup msg box then it will do the following...
'Copy Project number to the Archives sheet on the next avalable row
Range(Cells(Target.Row, 1), Cells(Target.Row, 9)).Copy
Sheets("Archives").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Else
Cells(TRow, TCol) = ""
End If


'this will match the requisition number in the archives sheet
If LCase(Target.Value) = "cancelled" Then
RptProjRowNum = Application.WorksheetFunction.Match( _
ActiveSheet.Range("A" & TRow).Value, _
Worksheets("Archives").Range("A5:A1000"), 0) + 4

'Add Comments to Archives
Worksheets("Archives").Range("V" & RptProjRowNum).Value = _
Range("K" & TRow).Value

'Add's the file comment to the Archives vs the same requisition number
Worksheets("Archives").Range("W" & RptProjRowNum).Value = _
Range("L" & TRow).Value

'This clears the content in the selected row from A to M
Application.EnableEvents = False
Range(Cells(Target.Row, 1), Cells(Target.Row, 13)).ClearContents
Application.EnableEvents = True
End If
End If
End If
End If
[/vba]
And replace the "Archives" sheet with the

[vba]
Workbooks.Open Filename:= _
"C:\file\folder\info\tech\DF\records\Work Load Report\Archives.xlsx"
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWindow.Close
[/vba]

This is complicated... Been trying all week to try to figure out how to replace it with my new request.
Reason is that I want to minimize the data and have all archives information transferred to another workbook.
Could someone please help?[/font]

[VNouBA]

Here is my workbook (Main)

I think this will be better for understanding... I only have two pages complete with the codes.

Do not look at the process but the code as this could take me a day to explain the process. In looking at the code I have added the explanation on most of them.

The path that when the user select "cancelled" needs to be the following:

"C:\file\folder\info\tech\DF\records\Work Load Report\Archives.xlsx"

In this Archives.xlsx the sheet and tab are called also Archives. (There is only one tab in the "Archives.xlsx" workbook).

[VNouBA]

Here are the functions that the code needs to be doing vs. what it is doing presently:
This example is only when the user select “cancelled” from the drop down menu in Column M and select the “Yes” option from the popup msg box.
Doing:
1. The information from that selected row from A to I is transferred to the “Archives” sheet.
2. The information from Column K is transferred to the “Archives” sheet on the same row of the selected row from the “Pre-Requisition” sheet to match the information and to add it in Column V.
3. The information from Column L is transferred to the “Archives” sheet on the same row of the selected row from the “Pre-Requisition” sheet to match the information and to add it in Column W
4. The information from “Pre-Requisition” sheet of the selected row is cleared from A to M.

Should be doing:

1. The information from that selected row from A to I is transferred to the Archives.xlsm workbook on the next available “EMPTY” row. Therefore will need to open the Archives.xlsm workbook.
2. The information from Column K is transferred to the Archives.xlsm workbook on the same row of the selected row from the “Pre-Requisition” sheet (Main document) to match the information and to add it in Column V.
3. The information from Column L is transferred to the Archives.xlsm workbook on the same row of the selected row from the “Pre-Requisition” sheet (Main document) to match the information and to add it in Column W.
4. Save the Archives.xlsm workbook then close.
5. The information from “Pre-Requisition” sheet of the selected row is cleared from A to M.



I hope this helps... I would really need help on this