Tuesday, December 30, 2014

Macro for Importing all files in folder to active sheet


Macro for Importing all files in folder to active sheet..

Sub Data_Merge_All_Files_SelectFolder_NO_ADO()

Dim bookList As Workbook

Dim FileName As Variant

Dim n As Long

Dim disWB As Workbook



Set disWB = ActiveWorkbook

FileName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)

Application.ScreenUpdating = False



For n = LBound(FileName) To UBound(FileName)

Set bookList = Workbooks.Open(FileName(n))

Range("A2:Q50" & Range("A65536").End(xlUp).Row).Copy

disWB.Worksheets(1).Activate

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _

        SkipBlanks:=False, Transpose:=False



Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial

Application.CutCopyMode = False

bookList.Close

Next n

Application.ScreenUpdating = True


MsgBox "Done!!"

END SUB