i think this is complicated

[slam]

I have 8 separate spreadsheet files with a list of document numbers in them as follows: Column A is 'Doc #', Column B is 'Doc Title', Column C is 'Rev #' and Column D is 'Effec Date' (these are the header row). Unfortunately, the majority of the date fields are formatted as text though they'd need to be converted to date to allow a useful sort.

What I need to do is move the information in each of these separate 8 spreadsheets into just one spreadsheet then have anything with an effective date within the last 2 years removed and have it sorted by effective date (column D). The purpose of this is to determine which documents haven't been revised for more than 2 years.

Is it possible to do this so this new spreadsheet which we'd create always updates itself by taking the information from the 8 separate spreadsheets which themselves are constantly updated.

Thank you!

 

[jameskel]

I don't know how familiar you are with VBA, so I'll outline what you need to do.

'add a blank worksheet after last known worksheet
Worksheets.Add , Worksheets(Worksheets.Count)
worksheets(worksheets.count).name= "LastSheet"

'loop through each worksheet to copy entries
' starting with row 2
' then paste entries into blank worksheet
lastws= worksheets.count
for a=1 to lastws-1
worksheets(a).range("A2").currentregion.copy
if a=1 then
worksheets(lastws).activate
range("A1").select
else
lastrow=worksheets(a).cells(1,1).end(xldown).row
cells(lastrow+1,1).select
endif
Selection.PasteSpecial Paste:=xlPasteValues
next

'Now all entries transfered to LastSheet

'sort entries
worksheets(lastws).activate
lastrow=worksheets(a).cells(1,1).end(xldown).row
'change columns on next line to suit needs
Range("A1:H" & lr).Select
'change next line Key1= to suit needs. Select the column that you wish to sort
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom

Hopefully you can take it from here.