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

 

[shwetankbhardwaj]

Thanks a lot Fluff. It's working good.

 

[Fluff]

Glad to help & thanks for the feedback

 

[shwetankbhardwaj]

Hi Fluff,

Hope you are doing great!

A small query related to what I have sent you above.

below coding helps to fetch only excel format .xlsx, however I also need to add .xls format but unable to add in the coding. It could a tiny stuff but very challenging for me.

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 & "*.xlsx")
Do While Fname <> ""
Workbooks.Open Filename:=path & Fname
shtname = Left(Fname, Len(Fname) - 5)
Sheets(1).Copy After:=ThisWorkbook.Sheets(1)
Workbooks(Fname).Close
ActiveSheet.Name = shtname
Fname = Dir()
Loop


Application.ScreenUpdating = True


End Sub

Thanks,
Shwetank

 

[Fluff]

Change this

Fname = Dir(path & "*.xlsx")

to

Fname = Dir(path & "*.xls[COLOR=#ff0000]*[/COLOR]")

 

[shwetankbhardwaj]

Hi Fluff,

I meant to say, I want both the formats .xlsx and .xls but not able to add both.

 

[Fluff]

If you change the x to * as per post#14, that should open any files with a extension beginning .xls
ie it should open any .xls, .xlsx. xlsm etc

 

[shwetankbhardwaj]

Hello Fluff,

I have posted my query twice but no one replied. So I finally I came back to you. I hope you will get a chance to look into the query.

I have a master worksheet which successfully import sheets and filtered data.

The problem here is, I want all the imported sheet (except master sheet) to be pasted in new worksheet name "consolidate" with specific columns and filtered rows.

Below is the coding used to import data and create a new tab (consolidate). But in addition I need:-

1. Only filtered rows. (currently getting the whole data)
2. Only A, B, C and F columns (as of now it gives all the columns)
3. Headers in all imported sheets are same, hence very first row of consolidate sheet to have headers. (no headers gets pasted as of now)
4. If any sheet doesn't show any data and only headers are shown in any of the sheet, than coding below paste headers for that specific file.

Coding:-
Sub copyfromworksheet()

Dim wrk As Workbook
Dim sht As Worksheet
Dim trg As Worksheet
Dim rng As Range
Dim colcount As Integer

Set wrk = ActiveWorkbook

Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
trg.Name = "Consolidate"
Set sht = wrk.Worksheets(1)
colcount = sht.Cells(1, 255).End(xlToLeft).Column

For Each sht In wrk.Worksheets
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If

Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colcount))
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value

Next sht

trg.Columns.AutoFit

Application.ScreenUpdating = True

End Sub

Thanks in advance!

Shwetank