Paul,
A formula can't do what you want. A formula can only effect the cell in
which the formula resides. You need a macro like the one below. To use the
macro you will first need to insert it into a VBA module. To start the VBE
(Visual Basic Editor) type Atl-F11. You should see a list of your open files
to the left. This is the Project Explorer. if you can't see it go to View,
Project Explorer.
Find your file and right click on the bold title. Click Insert, Module.
You should see a blank white space to the right. Cut the macro from below
and paste into the blank space. The words should turn different colors like
blue, green, and black.
From your file in Excel you can now access this macro by typing Atl-F8.
Double click on the macro entitled "SummaryTab". It should work.
Remember that before you run the macro there can be no tab named Summary in
your file or you will get an error. If you will be running the macro from a
new file every 10 days you will want to copy it to your personal.xls workbook
instead. If that is not one of the options in the Project Explorer window in
the VBE you can create a personal.xls by going back to Excel, selecting
Tools, Macro, Record New Macro, selecting Store Macro In: Personal Macro
Workbook, typing any old garbage in a cell, and clicking the Stop Recording
icon on the miniture toolbar that should have popped up. When you go back to
the VBE, personal.xls should be one of the files in the Project Explorer
Window. Double click on Modules, Module1 and copy the macro below over the
recorded macro you see to the right.
This workbook will open up each time you start Excel, but it will be hidden.
Any macros stored here will be available when you hit Alt-F8 in Excel.
Sub SummaryTab()
'Add summary sheet
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Summary"
'Copy title row to summary sheet
Sheets(1).Activate
Rows(1).Copy
Sheets("Summary").Activate
Rows(1).Select
ActiveSheet.Paste
'Start in next available cell
Range("A2").Select
For i = 1 To ActiveWorkbook.Sheets.Count - 1
'Goto each sheet and select entire range except titles
With Sheets(i).Range("A1").CurrentRegion
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy
'Paste to summary sheet
ActiveSheet.Paste
'Select next available cell
ActiveCell.End(xlDown).Offset(1, 0).Select
End With
Next i
'Sort by ID number
Range("A1").CurrentRegion.Sort Key1:=Range("F2"), Order1:=xlAscending,
Header:=xlYes, _
Orientation:=xlTopToBottom
End Sub
"Paul Cooke" wrote:
I would be really grateful if anyone could help me with this problem in
Excel please...It's driving me nuts !! Please bear in mind i am a
complete novice as far as formulas are concerned so would be grafteful
if any answers are given in a simply way for a simple guy!!!
I have multiple worksheets named "day1, Day2, Day3.....Upto Day10"
The columns are named as below
A = Location
B = Event
C = Name
D = Start
E = Finish
F = ID
The number of rows on each sheet can vary each day.
What i would like to do is to insert a sheet called "Summary" at the
end of the workbook and have a "formula" which will firstly...
Copy all the rows from each sheet to the "summary" sheet, once
copied....
Sort all the rows by Column F "ID"
I hope this is firstly possible !! but also explained quiet well.
Many thanks for any help or advice given
Best regards
Paul
--
Paul Cooke
------------------------------------------------------------------------
Paul Cooke's Profile: http://www.excelforum.com/member.php...o&userid=29268
View this thread: http://www.excelforum.com/showthread...hreadid=491675