Combine all sheets to Master sheet

[rahulwithu]

Hello

I've excel file in which 8 sheets with lots of data.
Can you please help me in making a master file (in 1 sheet, all data from 8 sheets)?

And master file should be up to date as soon as the changes in data of 8 sheets are made.

Thank you in advance
Rahul

[mdmackillop]

Copy and Paste Special with Paste Link

[rahulwithu]

Hello
Can you please explain this in detail?

[mdmackillop]

Copy your data from a sheet, go to the master sheet, select the target cell. then Edit/Paste Special. Click on PasteLink on this form. Repeat for each sheet.

[rahulwithu]

Thanks for your reply

Can't i automate this process, like if i enter data in 1 sheet it should automatically be updated in the master file?

[sidm]

This will combine all the data only at the time that the macro is run

Sub CombineAllDataonSheets()
Dim PasteRange As Range
Dim i As Long
Application.ScreenUpdating = False
Set PasteRange = Worksheets(1).Range("a65536") _
.End(xlUp).Offset(1, 0)
For i = 2 To Worksheets.Count
Worksheets(i).UsedRange.Copy PasteRange
Set PasteRange = Worksheets(1).Range("a65536") _
.End(xlUp).Offset(1, 0)
Next i
End Sub

[brettdj]

You could follow this article I wrote at experts exchange

The code provides three options:
1) Collate all sheets from all Excel workbooks in a single folder into a single summary worksheet
2) Collate all sheets from all Excel workbooks in a single folder into a single summary workbook
3) Collate all sheets from a single Excel workbook into a single summary worksheet

You would use the more basic option (3) - on a closed file - but the more advanced functionality may come in handy later

Cheers

Dave

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2804-Code-to-compile-all-worksheets-from-either-a-all-Excel-workbooks-in-a-folder-or-b-a-specific-Excel-workbook.html