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