Seemingly Simple Problem

AcTiVation

New Member
Joined
Mar 4, 2002
Messages
8
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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
Back
Top