VBA code to index match on external workbook

[stephsings]

Or anything similar...


I am trying to copy (from Workbook A) and paste in Workbook B.. if there is a two criteria match.
I was leaning towards using INDEX+MATCH.. but I was unable to get any result.
Essentially am tasked with going through a bunch of workbooks and trying to find the status using two criteria.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sub AllWorkbooks()
   Dim MyFolder As String  'Path collected from the folder picker dialog
  
   Dim MyFile As String 'Filename obtained by DIR function
   Dim wbk As Workbook 'Used to loop through each workbook
   Dim wb As Workbook: Set wb = ThisWorkbook
   Dim lvl As Integer
   Dim head As String
    lvl = wb.Worksheets("Sheet 1").Range("U2").Value
    head = wb.Worksheets("Sheet 1").Range("U3").Value
   Dim dte As Date 'Date
   Dim Sht As String 'Shift
   Dim Act As String 'Activity
  
On Error Resume Next
Application.ScreenUpdating = False
'Opens the folder picker dialog to allow user selection
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
   If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
MsgBox "You did not select a folder"
      Exit Sub
   End If
MyFolder = .SelectedItems(1) & "\" 'Assign selected folder to MyFolder
End With
MyFile = Dir(MyFolder) 'DIR gets the first file of the folder
'Loop through all files in a folder until DIR cannot find anymore
Do While MyFile <> ""
    'Opens the file and assigns to the wbk variable for future use
   Set wbk = Workbooks.Open(FileName:=MyFolder & MyFile)
   dte = wbk.Worksheets("logbook").Range("C4").Value
   Sht = wbk.Worksheets("logbook").Range("I4").Value
    'Find Level and Heading in Logbook
  
    Act = Application.WorksheetFunction.Index(wbk.Worksheets("logbook").Range("E72:E300"), (Application.WorksheetFunction.Match(1, (lvl = wbk.Worksheets("logbook").Range("B72:B300") * head = wbk.Worksheets("logbook").Range("D72:D300")), 1)))
       
  
   'Append to Table'
   wb.Worksheets("Sheet 1").Range("A2", Range("A2").End(xlDown)).Sort Key1:=Range("A1"), Order1:=xlDescending
   wb.Worksheets("Sheet 1").ListObjects("Table1").ListRows.Add 1



   wb.Worksheets("Sheet 1").Range("A2") = dte
   wb.Worksheets("Sheet 1").Range("B2") = Sht
   wb.Worksheets("Sheet 1").Range("C2") = lvl
   wb.Worksheets("Sheet 1").Range("D2") = head
   wb.Worksheets("Sheet 1").Range("E2") = Act
  
   'Replace the line below with the statements you would want your macro to perform
       
       
wbk.Close savechanges:=True
MyFile = Dir 'Dir gets next file in loop
Loop
Application.ScreenUpdating = True
wb.Worksheets("Sheet 1").Range("A2", Range("A2").End(xlDown)).Sort Key1:=Range("A2"), Order1:=xlAscending
End Sub

 

[ExcelGzh]

I hate to be critical seeing as this is your very first post, however you will need to explain what the criteria for determining the status actually are. It is extremely difficult to try and decipher the thinking of a programmer from the code they have written. Examining programming code can usually only tell you about programming code logic errors, not programmer thinking logic errors.