i think this is complicated

slam

Well-known Member
Joined
Sep 16, 2002
Messages
779
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!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jameskel

New Member
Joined
Sep 17, 2002
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top