Match function rejecting as invalid property assignment

[Ochimus]

Formula finds the name in Col AO in a sheet in a different workbook that matches the name in A5 of the active sheet, and pulls across the matching value from Col AP of the sheet in that other file. Works perfectly.

=IF(A4="","",IF(A5="",SUM(B4:B$5),IFERROR(INDEX(OTHERFILENAME]SHEETNAME!$AP$5:$AP$24,MATCH($A5,'OTHERFILENAME]SHEETNAME'!$AO$5:$AO$24,0)),"")))

But, as the other file name will change each month, need this as a Macro, allowing the end user to select the file

Sub Mergetimes()
 
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet

Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet

'Open file with data to be copied
    
    vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
    "*.xl*", 1, "Select Excel File", "Open", False)
    
    'If Cancel then Exit
    If TypeName(vFile) = "Boolean" Then
        Exit Sub
    Else
    Set wbCopyFrom = Workbooks.Open(vFile)
    Set wsCopyFrom = wbCopyFrom.Worksheets(2)
    End If
    
'--------------------------------------------------------------
'This is where the Index Match falls over

'Tried it as a VBA call, but doesn't recognise "Match", for some reason.

Range("B5") = Application.WorksheetFunction.Index(Workbooks([wbCopyFrom]), Worksheets(wsCopyFrom)![AP5:AP24], Application.WorksheetFunction.Match(Range("A5"), Workbooks([wbCopyFrom]), Worksheets(wsCopyFrom)![A05:A024], 0), 1)
 
'Then tried to convert the formula to accomodate the variable file and sheet names, but can't capture the correct syntax?

Range("B5")="=IF(A4="""","""",IF(A5="""",SUM(B4:B$5),IFERROR(INDEX('"&[wbCopyFrom]&wsCopyFrom!"&AP5:AP24,MATCH(A5,('"&wbCopyFrom&"]&wsCopyFrom'!&AO5:AO24,0)),"")))

Any suggestions or pointers received gratefully as ever

Ochimus

[Norie]

For one thing wsCopyFrom is a reference to a worksheet in the workbook that's been opened, it doesn't need a workbook reference.

As for the rest of the code, it's pretty confusing to be honest.

For example what are you trying to do here?

Range("B5") = Application.WorksheetFunction.Index(Workbooks([wbCopyFrom]), Worksheets(wsCopyFrom)![AP5:AP24], Application.WorksheetFunction.Match(Range("A5"), Workbooks([wbCopyFrom]), Worksheets(wsCopyFrom)![A05:A024], 0), 1)

You can't use an entire workbook/workhsheet as the first argument for Index/Match.

Is this what you are trying to do?

Range("B5").Value = Application.Index(wsCopyFrom.Range("AP5:AP24"), Application.Match(Range("A5"), wsCopyFrom.Range("A05:A024"), 0), 1)

[Ochimus]

Norie,

Apologies for not responding sooner.

Your assumption about what I am trying to do was exactly right, but for some reason I still can't get it to work properly.

Attached is a "Combined Hours" file that needs to pull data from the "Site" sheet in the "Site 1" file attached (I have moved the columns to the beginning, rather than leaving them in the actual columns they occupy in the "real" files).

1. User opens "Combined Hours" file and runs macro.

2. Macro allows user to select and open a file (the name of which will change each month). In this example it's "Site 1".

3. Macro then uses Index Match to put into B2 - D23 of the "Combined Site" sheet whatever values in Cols B - E of the "Site" sheet in the other file match the employee name in Col A.

For whatever reason, it's escaping me completely.

I know it should be straightforward, but hope someone can show me the wood for the trees?

Ochimus

[Ochimus]

Anybody out there can help me?

Ochimus