Seemingly Simple Problem

[AcTiVation]

Is it possible to have a dynamic range of data in different files all copied (values only) into a single spread sheet as soon as the target spread sheet is opened and then have the data that was pasted (dymnamic of course) sorted by date (high to low) which would be in say column "D"

Thanks in advanced,
regards,
ATV

[Cosmos75]

This might help get you started. You do need to have a sheet named Summary or change Summary to the name of the worksheet you want the ranges copied to

Sub CopyMultipleRange()
Dim ws As Worksheet

'Clears all data on sheet named Summary!! Make sure you don't have anything you need on it!
Worksheets("Summary").UsedRange.Delete

For Each ws In Worksheets
If ws.Name <> "Summary" Then
ws.Select
'Copies Range A1 to the first cell with data from the bottom in column be, change as required
'If you had data in Cells A1:B5 and something in B9 the range selected would be A1:B9
ws.Range("A1", Range("B65536").End(xlUp).Address).Copy
'Selects sheet named Sumary
Sheets("Summary").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
End If
Next ws
Sheets("Summary").Select
'Deletes First row on Summary sheet which is empty
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub

You could possibly add more to this macro to do the other things you need done to it.

Maybe something like

Columns("A:B").Select
'This says it has a Header Row.
Range("B1").Activate
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

If there is no Header Row, then change one line to

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

[ This Message was edited by: Cosmos75 on 2002-03-18 14:38 ]