Macro for copying and pasting workbooks into new sheets

[elayem]

Hi,

Please help with this problem. I tried searching other posts for answers as well.

I have a main workbook, Main.xlsm

I want to have a macro in there that can loop through and import data from whatever xls files are in a specific folder into new worksheets. The folder is "C:\Users\elayem\Downloads\Import"

For example if I have 3 workbooks in there:
workbook1.xls
workbook2.xls
workbook3.xls

The macro would import each workbook into 3 new worksheets in Main.xlsm.
Order does not matter. Naming of the new worksheets does not matter.

Can anyone help with this script? Thank you in advance!

 

[pvr928]

Hi elayem

Power Query will accomplish this with ease, and there is no need for VBA: https://www.powerquery.training/

Cheers

pvr928

 

[JoeMo]

But, in case you don't have Power Query, here's a macro for Main.xlsm you can try. Untested, so may need some tweaking. Assumes you have only one sheet to be copied from the imported workbooks, and that sheet is active when the workbook is opened, and the imported sheets don't all have the same tab name.

Sub elayem()
Dim pStr As String, myFile As String
pStr = "C:\Users\elayem\Downloads\Import\"
myFile = Dir(pStr & "*xls")
If myFile = vbNullString Then
    Exit Sub
Else
       Workbooks.Open pStr & myFile
       With ActiveSheet
              .Move after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
              .Parent.Close savechanges:=False
       End With
End If
Do
       myFile = Dir()
       If myFile = vbNullString Then
              Exit Do
       Else
              Workbooks.Open pStr & myFile
              With ActiveSheet
                     .Move after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                     .Parent.Close savechanges:=False
              End With
       End If
Loop
End Sub