VBA loop using two arrays

[Legacy 93538]

Hi

This is probably a very easy question to solve but i need to create a loop which loops through two arrays. The loop should first loop through an array of path name variables to open the files then it should copy data from the file opened and paste it into another workbook using a second array for the sheet name.

I am sorry if i have not explained that very well.

Does anyone know how to do this as i am not sure??

Thank you

Jessicaseymour

 

[Legacy 93538]

I had a go at creating the loop, however i get a type mismatch error:

'Define Path Array
PathArray = Array("Path1", "Path2", "Path3", "Path4", "Path5", "Path6", "Path7", "Path8")
SheetArray = Array("BGBL", "HUBK", "HUK", "BMW", "HBE", "HNL", "IMG", "MAN")
'Loop though path files and copy data
For X = 1 To UBound(PathArray)
    For Y = 1 To UBound(SheetArray)
        Set CCTLWB = Application.Workbooks.Open(X & "\" & "*TrackerLog*" & ".xlxs")
        Set CCTLWBCSht = CCTLWB.Sheets("Commentary")
        Set CCTLWBCLSht = CCTLWB.Sheets("CallLog")
        L = WorksheetFunction.CountA(CCTLWBCSht.Range("A:A"))
        CCTLWBCSht.Range("A" & L & "N" & L).Copy Destination:=PPCWB.Sheets(Y).Range("A1")
        
        'CCTLWBCSht.Range("A" & L & "N" & L).Copy Destination:=PPCWB.Sheets(Y).Range("A4")
            
    Next Y
Next X