Consolidate sheets(+new added sheets)into Master sheet+sum by date Customer

[guatelize]

I'm using a workbook containing a worksheet with same layout for each customer with their orders & delivery quantities by date and means of transport. I want to consolidate all the data from the different worksheets into one worksheet(Master), where update has to be automatically by selecting MasterSheet. Other sheets are added weekly with new customers. I have been looking everywhere to consolidate with a macro or udf, but could not find the exact solution. Can someone help me ? I've attached the link for the file. thanks a lot.


https://docs.google.com/file/d/0B6ngdl529IBTY1ltZ0pmMTZxXzQ/edit?usp=sharing

 

[bobhc]

Good day quatelize


This link should be of help


http://www.rondebruin.nl/copy2.htm

 

[guatelize]

I tryed already with rondebruin macro, but could not find a solution for the summuray of totals (formula)within my file.

 

[akmalkhan]

u will have to create a sheet titled 'Master' for this macro to work.( without the ' ')


Sub CombineData()

Dim Sht As Worksheet

For Each Sht In ActiveWorkbook.Worksheets

If Sht.Name <> "Master" Then

Sht.Select

Range("A:A").Insert

Range("A26").Formula = "=Mid(Cell(""filename"",B1),Find(""]"",Cell(""filename""))+1,255)"

Range("A26").Copy

Range("A26").PasteSpecial Paste:=xlPasteValues

Range("A26:L26").Copy 'mention the range you want to copy from each sheet.

Sheets("Master").Select

Range("A65536").End(xlUp).Offset(1, 0).Select

ActiveSheet.Paste

Sht.Select

Range("A:A").Delete

Else

End If

Next Sht


End Sub

 

[guatelize]

Thanks for your help. It works.

 

[akmalkhan]

cheers.