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]

My pleasure.