Hi ictfc
This Code compiles and runs...have no clue it it does as you require.
Sub test()
Filepath = ("filepath on my computer\")
Set ws = Sheets("Sheet1")
n = ws.Range("car").Count
m = ws.Range("type").Count
p = ws.Range("reg").Count
j = 1
i = 1
k = 1
For j = 1 To n
For i = 1 To m
For k = 1 To p
cr = ws.Range("car").Cells(j, 1)
ty = ws.Range("type").Cells(i, 1)
rg = ws.Range("reg").Cells(k, 1)
file2 = ("\ABC-123-" & rg & "-" & ty & "-" & cr & "-CHECK.xlsm")
If FileFolderExists(Filepath & file2) Then
Workbooks.Open (Filepath & file2)
ActiveWorkbook.Worksheets("CarInfo").Visible = True
ActiveWorkbook.Worksheets("CarInfo").Select
ActiveWorkbook.Worksheets("CarInfo").Range("F1:F87").Select
Application.CommandBars.FindControl(ID:=398).Execute
Application.CommandBars.FindControl(ID:=398).Execute
Selection.Copy
ActiveWorkbook.Close (False)
ActiveSheet.Cells(1 + (i + (n + 2) * (j - 1)), 2).Select
Selection.PasteSpecial
Application.CutCopyMode = False
End If
Next k
Application.CutCopyMode = False
Next i
Application.CutCopyMode = False
Next j
End Sub
Public Function FileFolderExists(strFullPath As String) As Boolean
'Author : Ken Puls (www.excelguru.ca [1])
'Macro Purpose: Check if a file or folder exists
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
EarlyExit:
On Error GoTo 0
End Function
Bookmarks