Combining spreadsheets to a master sheet

[miller_leann]

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??

 

[JLatham]

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