excel help needed

[edinburgh]

Hi Excel experts,

I'm looking for excel macro in order to combine sheets from multiple workbooks to one workbook.

Example:

Workbook1

sheet1 | sheet2 | sheet 3

Workbook 2

sheet1 | sheet2 | sheet 3

/// sheets are having same headers

i.e Sheet 1 in workbook1

Header: Site name | Site ID | Lat | Lon

Sheet 1 in workbook2

Header: Site name | Site ID | Lat | Lon


Output: final workbook

sheet1 | sheet2 | sheet 3


Thank you so much for your contribution.

Edin

[TIGRI007]

search on Google
:o

[Ghost78]

Hi;;

Towmorrow I will send you one macro doing exactly what you want.

Best Regards.

[tech_frk]

hi edinburgh,

try RDB Merge Addin to excel , it works great. no coding required.

[edinburgh]

I've tried with RDB but the result is not what I want..waiting for coding..thanks everyone...

[TIGRI007]

Please check again the option it should work like you asked because I have already tested With one sheet

[Ghost78]

Dears;

please download the following excel macro, it was created mainely to combine many sheets from CDDs, but it works perfectly fo any kind of excel sheet combination.
stepes of work with the macro:

ensure that all files contains the sheet you want to merge with same name, and same headers
open one of your exel files to be merged, than open this macro.
on your open file, select the sheet you want to merge, right click, click on "move or copy".
a new window appears, from "to book" window select the open macro "CDD combine", select "move to end" option, and check "Create a copy" to do not loose the sheet from your file.
close your file.
go to macro now, click on combine, select all your file, and lick next, you wil get all sheets combined in new sheet added to this macro.
if you want to combine another time, make sure that there is no filter in all sheets, then click clear, to remove all registred values, then click combine again.
to add new sheet, follow the previous steps, you can add as many sheets as you want.

http://www.4shared.com/file/OSav7j3lce/CDD_Comb.html?

Best Regards.

[edinburgh]

wah...thank you so much..this is exactly what I need...

If there are several sheets in workbooks, then how can I combine it?

Testing is ok for one sheets, but hows' about for many sheets?

anyway thank you so much...Appreciated

[Ghost78]

Welcome;

If there are several sheets, add them one by one to the macro following the steps I explained perviousely, and click combine.

Regards.

[SAMI12]

i found this small tools and i think this is helpful for you

https://www.youtube.com/v/HVdBkce83O0?hl=en_US&version=3&rel=0&vq=large&wmode=transparent

tools:

[Ghost78]

Hi;

This merges only the first sheet, right!!, I have files contains a lot of sheets, sometimes I want to merge a specific one, please can you show us how to do it using this tool.

Best Regards.

[SAMI12]

Hi;

This merges only the first sheet, right!!, I have files contains a lot of sheets, sometimes I want to merge a specific one, please can you show us how to do it using this tool.

Best Regards.

you are right this tools cant allow s you to copy specific sheet but if you want there is a simple macro allow you to do that but you need to edit the path
and the rows manualy. ;)

macro code :


Sub XlsMerger()
Dim bookList As Workbook
'Add 2 new variables for sheets count
Dim index As Integer

Dim mergeObj, dirObj, filesObj, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

Set dirObj = mergeObj.Getfolder("C:\path to folder with excel file\")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)





For index = 1 To Sheets.Count



'set active sheet trhough iteration and copy
bookList.Worksheets(index).Activate
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy

'move to target workbook, related sheet and paste
ThisWorkbook.Worksheets(index).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False

'Next sheet
Next index

bookList.Close
Next
End Sub

[edinburgh]

can someone work the above script into excel and post it here..please?
I've tried it and it seems not working...