merging files in vba

[lezawang]

Hi
I have a file called AllEmployees.xlsx which has one sheet called ALL and inside that sheet an empty table with only labels as below. I have 3 other excel files sales.xlsx, marketing.xlsx and hr.xlsx, each file has one sheet (sales, marketing, hr) and each one has one table as below. I want to write a vba code that move these sheets from sales, marketing and hr files to the AllEmployees files as separate sheets. So the AllEmployees file will have these sheets (ALL, sales,marketing, hr) and then combine all these tables and put them in one table in "All" sheet.


I just watched a video about this but no code was there. https://www.youtube.com/watch?v=Svp9z9PDEIk
I would like to learn how it cam be done. Any help would be appreciated very much. Thanks


AllEmployees.xlsx (sheet All)

[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]sex[/TD]
[TD]age[/TD]
[TD]dept[/TD]
[TD]salary[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sales.xlsx (sheet sales)

[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]sex[/TD]
[TD]age[/TD]
[TD]dept[/TD]
[TD]salary[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]m[/TD]
[TD]50[/TD]
[TD]sales[/TD]
[TD]20k[/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD]f[/TD]
[TD]40[/TD]
[TD]sales[/TD]
[TD]40k[/TD]
[/TR]
</tbody>[/TABLE]

marketing.xlsx (sheet marketing)

[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]sex[/TD]
[TD]age[/TD]
[TD]dept[/TD]
[TD]salary[/TD]
[/TR]
[TR]
[TD]mike[/TD]
[TD]m[/TD]
[TD]44[/TD]
[TD]marketing[/TD]
[TD]100k[/TD]
[/TR]
[TR]
[TD]anna[/TD]
[TD]f[/TD]
[TD]30[/TD]
[TD]marketing[/TD]
[TD]50k[/TD]
[/TR]
</tbody>[/TABLE]


hr.xlsx (sheet hr)

[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]sex[/TD]
[TD]age[/TD]
[TD]dept[/TD]
[TD]salary[/TD]
[/TR]
[TR]
[TD]sara[/TD]
[TD]f[/TD]
[TD]40[/TD]
[TD]hr[/TD]
[TD]40k[/TD]
[/TR]
[TR]
[TD]eric[/TD]
[TD]m[/TD]
[TD]50[/TD]
[TD]hr[/TD]
[TD]50k[/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

Are the Sales, Marketing and hr files already open or does the macro have to open them? If the macro has to open them, I would need the full path to the folder that contains them.

 

[lezawang]

Thank you for your reply. They are closed and they are all in the same location c:\myexcelfile\
Thanks again.

 

[mumps]

Are they the only workbooks in that folder with an "xlsx" extension?

 

[lezawang]

No there are others as well. Thank you for your reply.

 

[mumps]

Place this macro in a standard module in the AllEmployees workbook. Save the workbook as a macro-enabled file. Run the macro.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim i As Long, wbArray As Variant, wsDest As Worksheet, wkbSource As Workbook, wkbDest As Workbook
    Set wkbDest = ActiveWorkbook
    Set wsDest = wkbDest.Sheets("ALL")
    wbArray = Array("Sales.xlsx", "marketing.xlsx", "hr.xlsx")
    For i = LBound(wbArray) To UBound(wbArray)
        Set wkbSource = Workbooks.Open("C:\myexcelfile\" & wbArray(i))
        With wkbSource
            If .Name = "Sales.xlsx" Then
                .Sheets("sales").Copy after:=wkbDest.Sheets(wkbDest.Sheets.Count)
                .Sheets("sales").UsedRange.Offset(1, 0).Copy wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1, 0)
            ElseIf .Name = "marketing.xlsx" Then
                .Sheets("marketing").Copy after:=wkbDest.Sheets(wkbDest.Sheets.Count)
                .Sheets("marketing").UsedRange.Offset(1, 0).Copy wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1, 0)
            ElseIf .Name = "hr.xlsx" Then
                .Sheets("hr").Copy after:=wkbDest.Sheets(wkbDest.Sheets.Count)
                .Sheets("hr").UsedRange.Offset(1, 0).Copy wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        End With
        wkbSource.Close False
    Next i
    Application.ScreenUpdating = True
End Sub

 

[lezawang]

Wow, working amazing. Thank you so much. I will learn a lot from this code. Thanks once again.

 

[mumps]

You are very welcome.

 

[lezawang]

Thank you once again and sorry for bothering you again. The code is working but only Sales are not moving to the main workbook. So in the main workbook, I have ALL, marketing, hr . but not Sales. and in ALL sheet. I have table that shows marketing and hr data but not sales. Why is that? thank you again

 

[lezawang]

Please ignore my previous message. It works amazing. File sales.xlsx was in lower case. I changed it to upper case and works so beautifully. Thank you again and again.