Import multiple excel files into one master workbook

[shwetankbhardwaj]

Hi,

I have imported multiple excel files into one master workbook but also want imported files to have its actual name instead of Sheet1..2.. and so on. e.g I have imported two files named "Customer name" and "Quantity" but in master workbook these can be seen as Sheet1 and sheet 2. Please help!

Below is the language I have used to Import files & working successfully.

Sub GetSheets()


Path = "O:\Funds\Other\Sales load activity"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

 

[Fluff]

Do the files you are trying to import have 1 sheet only?

 

[shwetankbhardwaj]

Hi Fluff,

Yes all the files (which are to be imported) have only 1 sheet.

Thanks for looking into this.

 

[Fluff]

Ok try this

Sub GetSheets()

Dim Path As String
Dim FName As String
Dim ShtName As String

Application.ScreenUpdating = False

    Path = "O:\Funds\Other\Sales load activity"
    FName = Dir(Path & "*.xls")
    Do While FName <> ""
        Workbooks.Open FileName:=Path & FName
        ShtName = Left(FName, Len(FName) - 4)
        Sheets(1).Copy After:=ThisWorkbook.Sheets(1)
        Workbooks(FName).Close
        ActiveSheet.Name = ShtName
        FName = Dir()
    Loop

Application.ScreenUpdating = True

End Sub

 

[shwetankbhardwaj]

You are amazing Fluff!

It's working with two minor changes.

1. I changed path from "O:\Funds\Other\Sales load activity" to "O:\Funds\Other\Sales load activity\"
2. File format is changed from "*.xls" to "*.xlsx".

Thanks a lot!!!

 

[Fluff]

Glad to help & thanks for the feedback

 

[shwetankbhardwaj]

Hey Fluff,

I would be needing your help again to find out value based on Sheet name. e.g in sheet1, It's written "Product" in "A2" cell in very next cell "B2" I will be needing an amount from next sheet (sheet name is product "C13". Also have more names listed down in sheet1 after product e.g quantity, Client name in A3 and A4 respectively and also have sheet named quantity and client name).

Thanks,
Shwetank

 

[Fluff]

Will you always be pulling the info from C13?
If not, how do you tell where to get the info from?

 

[shwetankbhardwaj]

No Fluff, Cell can be changed based on values. So every time I have go at end of the column C but yes column will always be C.

 

[Fluff]

Not to hot on formula but try this in B2 & fill down

=LOOKUP(9.99E+307,INDIRECT("'"&A2&"'!C:C"))