PDA

View Full Version : Combine all sheets to Master sheet



rahulwithu
07-12-2010, 12:22 AM
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
07-12-2010, 12:29 AM
Copy and Paste Special with Paste Link

rahulwithu
07-12-2010, 01:00 AM
Hello
Can you please explain this in detail?

mdmackillop
07-13-2010, 12:11 AM
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
07-15-2010, 11:53 PM
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
07-16-2010, 03:33 AM
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
07-20-2010, 04:57 AM
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