Thread: Help with copy/paste from many workbooks into one workbook

Hello again!
I've got 3 workbooks: try1.xlsx, try2.xlsx, try3.xlsx which looks pretty the same, same sheet names, same sheet numbers, similar columns with similar names, just data is different.
What I need to do is to copy from each sheet two columns and paste it into new workbook into new sheet for each workbook (so I will get 3 new worksheets: "try1","try2","try3"). All columns should appear next to each other.
So far I got a code which ,when run inside workbook, it will copy 2 columns from each sheet and paste them next to each other into "sheet2" inside same workbook.

Sub CopyColumn()

    Dim iCol As Long
    Dim iSht As Long

    ' Loop through columns
    For iSht = 1 To 6 ' no of sheets
    For iCol = 1 To 6 ' no of columns
        
        With Worksheets(iSht).Columns(iCol)
            
            If ((.Cells(1, 1).Value = "Time")) Then ' if first cell=Time then copy two columns
                Range(.Cells(1, 2), .End(xlDown)).Copy _
                    Destination:=Worksheets("Sheet2").Columns((iSht + 1) + iSht).Cells(2, 1)
                Worksheets("Sheet2").Cells(iSht * 2 + 1) = Worksheets(iSht).Name
            Else
                ' do nothing
                
            End If
        End With
        
    Next iCol
    Next iSht

End Sub

What I want to make is to make a kind of template workbook so, each time I open it, it will execute that code inside each of those 3 workbooks and paste it as a new sheet into template workbook. So at the end of operation I will have workbook number 4 with 3 sheets inside: "run1", "run2", "run3".

So far i managed to find a code which,after pressing button, letting me to choose a file and it does copy 2 columns but only from specified sheet.

Sub Button3()
    Dim excel As excel.Application
    Dim wb As excel.Workbook
    Dim sht As excel.Worksheet
    Dim f As Object

    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    f.Show

    Set excel = CreateObject("excel.Application")
    Set wb = excel.Workbooks.Open(f.SelectedItems(1))
    Set sht = wb.Worksheets("run1_walk")

    sht.Activate
    sht.Columns("C:D").Copy
    Range("Q3").PasteSpecial Paste:=xlPasteValues
    wb.Close
End Sub

Is there any chance to combine both codes, so every time i pressed the buttton it will let me choose the file and then copy columns into new sheet?

bump up. Can anybody answer it? Please?