Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining spreadsheets to a master sheet
I have 4 spreadsheets that have the same layout - they all contain
text and dates. For example, there are columns for Person, Action item, Date due. I need to create a 5th spreadsheet that combines all of these so I can do sorting by due date for everyone. I have tried a few things with no success: 1. copy individual spreadsheet and use paste special/paste link into sheet 5. This works for data that is already in the individual spreadsheet, but if I need to add a new row, it will not automatically be added to the summary sheet (sheet 5). I can copy extra blank rows at the bottom and paste them, but then I have all of these 0s in those cells on the summary sheet. 2. Data consolidation - this doesn't appear to work for what I want to do because I am not wanting to sum or avaerage anything. I simply want to allow people to update their individual sheets so that they are more manageable. However, I need the summary or master sheet so I can go in and see the big picture. any ideas?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining spreadsheets to a master sheet
The code below is by no means a fully tailored solution. What it would do is
rebuild your summary sheet on demand by first deleting everything currently on the summary sheet and then recopying all from the other sheets in the workbook onto the summary sheet and then sorting by the date. Since I don't know how your sheets are currently laid out, the code below is only an example of a starting point for you to use to get the job done. Sub RebuildSummaryData() 'change sheet name as needed Const SummarySheet = "Summary" ' the summary sheet name Dim anySheet As Worksheet Dim lastRow As Long Worksheets(SummarySheet).Select Application.ScreenUpdating = False Cells.Clear ' empties out old data! Range("A1").Select ' could be A2 if you want header row For Each anySheet In Worksheets If anySheet.Name < SummarySheet Then 'we will copy data from other sheets into 'the summary sheet, one sheet at a time anySheet.Select ActiveSheet.UsedRange.Select Selection.Copy Worksheets(SummarySheet).Select ActiveSheet.Paste 'get ready for next paste Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select End If Next 'ends the copying, leaves you on Summary sheet 'now do the sort 'this assumes dates are in column A 'and all data occupies columns A:R 'This assumes no header row, but that's 'probably not true - you're probably going 'to actually end up copying header rows from all '4 other sheets. 'adjust column references appropriately lastRow = Range("A" & Rows.Count).End(xlUp).Row Range("A2:R" & lastRow).Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Range("A1").Select Application.ScreenUpdating = True End Sub " wrote: I have 4 spreadsheets that have the same layout - they all contain text and dates. For example, there are columns for Person, Action item, Date due. I need to create a 5th spreadsheet that combines all of these so I can do sorting by due date for everyone. I have tried a few things with no success: 1. copy individual spreadsheet and use paste special/paste link into sheet 5. This works for data that is already in the individual spreadsheet, but if I need to add a new row, it will not automatically be added to the summary sheet (sheet 5). I can copy extra blank rows at the bottom and paste them, but then I have all of these 0s in those cells on the summary sheet. 2. Data consolidation - this doesn't appear to work for what I want to do because I am not wanting to sum or avaerage anything. I simply want to allow people to update their individual sheets so that they are more manageable. However, I need the summary or master sheet so I can go in and see the big picture. any ideas?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining information from 2 Spreadsheets into 1 | Excel Discussion (Misc queries) | |||
Automatically add records from a new sheet to master sheet | Excel Discussion (Misc queries) | |||
Combining data from multiple worksheets and separate spreadsheets | Excel Discussion (Misc queries) | |||
Master Sheet | Excel Worksheet Functions | |||
combining tabs in one master tab | Excel Discussion (Misc queries) |