Hi Tellm,
The zipped folder has three workbooks, one is the workbook that collects the info and two are sample workbooks each being an .xls or .xlsx file, and having data in A2:Q800 ranges on each sheet.
The one with the code is a macro enabled workbook and will not even show up on your list when you run the code.
When you click the button, you will open a getopenfilename dialog box. The filter only shows .xls or .xlsx files.
Open one of the workbooks and watch the magic!!!
Actually... there will be no magic to watch, once you open the workbook, it will close right away and you will wonder if anything happened.
Sub GetData()
Dim WB1 As Workbook, Wsht As Worksheet
Dim Sht As Worksheet
Dim FileToOpen As Variant
Set WB1 = ThisWorkbook
Set Wsht = WB1.Worksheets("Sheet1")
ChDir "C:\TestFolder" 'Change this directory
filt = "Excel (*.xls;*.xlsx),*.xls;*.xlsx,"
FileToOpen = Application.GetOpenFilename(filt, , "Open The Workbook")
If FileToOpen <> False Then
Workbooks.Open Filename:=FileToOpen
Else: MsgBox "Nothing Selected": Exit Sub
End If
Application.ScreenUpdating = False
For Each Sht In ActiveWorkbook.Worksheets
Sht.Range("A2:Q800").Copy Destination:=Wsht.Range("A65536").End(xlUp).Offset(1, 0)
Next Sht
ActiveWorkbook.Close
End Sub
Hope everything works out.
Be sure to change the file directory
Bookmarks