Cannot Open Matching Workbooks After Dropdown Selection

Question

INITIAL QUESTION

Why am I not able to open all (all three) matching workbooks?

Dropdown selection:

1A:1C = Company1 Company2 Company3

2A:2C = Version2 Version1 Version1

Only the first one (Company1, Version2) will open...

Sub OpenWorkbooks()

Dim ColumnIndex1 As Integer
Dim ColumnIndex2 As Integer
Dim ColumnIndex3 As Integer
Dim ColumnIndex4 As Integer
Dim ColumnIndex5 As Integer
Dim ColumnIndex6 As Integer

For ColumnIndex1 = 1 To 3
If Cells(1, ColumnIndex1).Value = "Company1" And Cells(2,      
ColumnIndex1).Value = "Version1" Then
Workbooks.Open Filename:="D:\Company1\Version1.xlsx"
End If
Next ColumnIndex1

For ColumnIndex2 = 1 To 3
If Cells(1, ColumnIndex2).Value = "Company1" And Cells(2,
ColumnIndex2).Value = "Version2" Then
Workbooks.Open Filename:="D:\Company1\Version2.xlsx"
End If
Next ColumnIndex2

For ColumnIndex3 = 1 To 3
If Cells(1, ColumnIndex3).Value = "Company2" And Cells(2,
ColumnIndex3).Value = "Version1" Then
Workbooks.Open Filename:="D:\Company2\Version1.xlsx"
End If
Next ColumnIndex3

For ColumnIndex4 = 1 To 3
If Cells(1, ColumnIndex4).Value = "Company2" And Cells(2, 
ColumnIndex4).Value = "Version2" Then
Workbooks.Open Filename:="D:\Company2\Version2.xlsx"
End If
Next ColumnIndex4

For ColumnIndex5 = 1 To 3
If Cells(1, ColumnIndex5).Value = "Company3" And Cells(2, 
ColumnIndex5).Value = "Version1" Then
Workbooks.Open Filename:="D:\Company3\Version1.xlsx"
End If
Next ColumnIndex5

For ColumnIndex6 = 1 To 3
If Cells(1, ColumnIndex6).Value = "Company3" And Cells(2, 
ColumnIndex6).Value = "Version2" Then
Workbooks.Open Filename:="D:\Company3\Version2.xlsx"
End If
Next ColumnIndex6

End Sub

I have just started using VBA (and StackOverflow).

Thank you.

FOLLOW-UP

@ Dirk Reichel: @ All:

I have tried to expand Dirk's idea a bit (See below), and I'm trying to open 5 (or less) workbooks in sequence each time copying/pasting a specific range to the 'main2' sheet of the 'main' workbook.

It works fine unless I open fewer workbooks than the number of dropdown values that are being checked (I'm currently using 5 dropdown sets instead of the original 3: see top of page):

Sub ImportData()

Dim MainWorkbook As Workbook
Dim DataWorkbook As Workbook
Dim i As Long

Set MainWorkbook = ThisWorkbook

With MainWorkbook.ActiveSheet

For i = 2 To 6

If ActiveSheet.Cells(6, i).Value <> "" Then

Set DataWorkbook = Workbooks.Open("D:\ 'some folders' \" & .Cells(6, 
i).Value & "-" & .Cells(10, 2) & "-" & .Cells(7, i).Value & ".xlsx")
DataWorkbook.Sheets("Sheet1").Range("C3:Q3").Copy
MainWorkbook.Sheets("Main2").Range("A" & i).PasteSpecial

On Error Resume Next

End If

Next i

End With

End Sub

I have used 3 of the (now) 5 dropdown menus, and only 1 workbook is currently being opened and copied...

Answer 1

You may try an easier script like this:

Sub OpenWorkbooks()
  Dim i As Long
  With ThisWorkbook.ActiveSheet
    For i = 1 To 3
      Workbooks.Open Filename:="D:\" & .Cells(1, i).Value & "\" & .Cells(2, i).Value & ".xlsx"
    Next i
  End With
End Sub

if your Cells do not have any "Workbook" and "Worksheet" they will do it with the active one (after opening the first workbook, all your Cells will refer to it and not to the orginal source)