VNouBA
09-21-2012, 09:50 AM
Good evening,
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.
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
This function gets the matching Requisition number in the "report" and archives" sheet.
I want to delete the second one (“Archives”) to replace it with the following but it’s giving me an error:
MsgBox Application.WorksheetFunction.Match(Range("A5:A10000"),
Workbooks.Open Filename:= _
"C:\file\folder\info\tech\DF\records\Work Load Report\Archives.xlsx"
Then I need help with the following:
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
And replace the "Archives" sheet with the
Workbooks.Open Filename:= _
"C:\file\folder\info\tech\DF\records\Work Load Report\Archives.xlsx"
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWindow.Close
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?
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.
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
This function gets the matching Requisition number in the "report" and archives" sheet.
I want to delete the second one (“Archives”) to replace it with the following but it’s giving me an error:
MsgBox Application.WorksheetFunction.Match(Range("A5:A10000"),
Workbooks.Open Filename:= _
"C:\file\folder\info\tech\DF\records\Work Load Report\Archives.xlsx"
Then I need help with the following:
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
And replace the "Archives" sheet with the
Workbooks.Open Filename:= _
"C:\file\folder\info\tech\DF\records\Work Load Report\Archives.xlsx"
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWindow.Close
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?