Hello
I do not have the same information as you , meaning that you have not been fully clear above, but what I do think is that you are overcomplicating matters.
I know, and I apologize. As I've said in the past, VBA is brand new to me and what I have so far is largely the result of piecing together solutions from other threads and knowledge bases. I'll try again, and yes, this is related to the issue you helped me with yesterday...
1. I will be exporting an inventory report from another program into excel format on a regular basis. The exported data will always be in the same format with the same headers, but the content will change.
2. I have two macros that need to be added to each new report in order for a hand scanner to work with the report. If both of them are located in the Sheet1 object they work properly.
3. I found a way in the thread linked to above to copy a module from one workbook to another using the following code:
Code:
Sub ImportModules()
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objfolder = objFSO.getfolder("C:\Module Project\ExcelFiles")
For Each f In objfolder.Files
Workbooks.Open f.Path, , False
ActiveWorkbook.VBProject.VBComponents.Import "C:\Module Project\script.bas"
ActiveWorkbook.Save
ActiveWorkbook.Close
Next
End Sub
This solution works, but because my macro 1 is imported into a module instead of the object Sheet1, the change event won't work when the KeyCell is changed.
4. What I'm trying to do then, is find a way to copy both of my macros into Sheet1 instead of a separate module.
I hope this makes sense, my macros are below.
Macro 1 checks a target cell for a change value. When it sees a change, it calls macro 2.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCell As Range
'The variable KeyCell contains the cell that will
'cause an alert when it is changed.
Set KeyCell = Range("K1")
If Not Application.Intersect(KeyCell, Range(Target.Address)) _
Is Nothing Then
'When the variable KeyCell changes, run the following macro
FindDuplicate
End If
End Sub
Macro 2 looks for the data in K1. If it finds it, it highlights the row containing the data. If it doesn't find it, it displays a message box to that effect.
Code:
Sub FindDuplicate()
Dim ICCR As String
Dim FoundCell As Range
'The variable ICCR contains the value that will be searched for
ICCR = Range("K1").Value
'Searches for the contents of the variable ICCR
On Error Resume Next
Set FoundCell = Range("K:K").Find(What:=ICCR, _
After:=Range("K1"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
'If the ICCR is not found, display a message box to the user
If FoundCell.Address(0, 0) = "K1" Then
ActiveSheet.Range("K1").Select
MsgBox "ICCR " & Range("K1") & " Not Found"
Exit Sub
'If the ICCR is found, highlight the corresponding row
Else
Rows(FoundCell.Row).Select
End If
End Sub
Hopefully I explained a little better this time around.