VBA code to index match on external workbook


New Member
Nov 9, 2020
Office Version
  1. 365
  2. 2019
  1. Windows
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.


VBA Code:
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"
.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
Application.ScreenUpdating = True
wb.Worksheets("Sheet 1").Range("A2", Range("A2").End(xlDown)).Sort Key1:=Range("A2"), Order1:=xlAscending
End Sub

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)


Board Regular
Mar 29, 2020
Office Version
  1. 365
  1. Windows
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.

Watch MrExcel Video

Forum statistics

Latest member
Sovereign maphoso

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back