I am going insane trying to work this out...

[Paul Cooke]

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

 

[Guest]

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]

Firstly many thanks for taking the time to reply and for explaining i
in a easy way!!

I have followed your instuctions to the letter and when i paste th
code to the module the last section is Red as shown below

'Sort by ID number
Range("A1").CurrentRegion.Sort Key1:=Range("F2"), Order1:=xlAscending,
Header:=xlYes, _
Orientation:=xlTopToBottom

I saved the code anyway and tried to run it and it cam up with a Synta
error and hi-lights the the row..

Range("A1").CurrentRegion.Sort Key1:=Range("F2"), Order1:=xlAscending


Is this something I am doing wrong?


Thanks agai

 

[Guest]

It looks like the text wrapped too soon. Try bringing "Header:=xlYes, _" up
to the line above it (make sure there is a space between the comma and the
word "Header"). Rows of code can only break onto a new line when they are
followed by a space and an underscore (as you can see follows "xlYes,"
above). Let me know if you continue to have trouble.

 

[Paul Cooke]

Hi Erin, I only just sorted that bit thanks for replying again, The cod
works brilliantly !!

If you don't mind can i ask a few other questions...

If i wanted to select a specific range of cells would i just change
the ("A1") bit to show the range?

and

If I wanted to add another column to the summary sheet to calulate th
time worked, can this be added to the code? I already have the code
need for this purpose which is

=ROUNDUP((E3-D3)*48,0)/2

Please ignore the cell references in this bit as its currently used o
another workbook only

I hope you don'y mind me asking

Kind regards

Pau

 

[Guest]

In both places where I used Range("A1") I just used that cell as an anchor so
you'll need to replace a bit more code with your desired range. For instance
if you want to specifically designate a range to copy you would replace
"With Sheets(i).Range("A1").CurrentRegion"
with "With Sheets(i).Range("A2:F6")". You then also would not need to
offset and resize the region as I did to avoid copying the title row. So you
would delete
".Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)" which I have in the
next line of code.

Remember that when you hard code a range it will not expand as your
worksheet expands. You mentioned that each day's sheet had a varying number
of rows. This syntax would not accomodate that.

You can also work with named ranges. For instance if your worksheet has a
range named "MyRange" you can reference this range in code.
Range("MyRange").Select will select your named range.

In answer to your second question, paste this code between the last line of
code and the line which reads "End Sub".

Range(Range("F2"), Range("F2").End(xlDown)).Offset(0, 1).FormulaR1C1 _
= "=ROUNDUP((RC[-2]-RC[-3])*48,0)/2"

To avoid the line break problem you had earlier ensure that this pastes as
two lines of code which break at the " _".

This code measures the number of rows from F2 to the bottom of the list
then, in the same number of cells in the column to the right (column G),
enters the formula you gave me using the cell that is on the same row and two
columns back (column E) and the cell that is in the same row and three
columns back (column D).

If you want to learn more about writing macros and other handy Excel
features John Walkenbach's book Excel 2003 Power Programming with VBA is a
good one. It's best if read cover to cover. It's a big book, but an easy
read. Visit his site at www.j-walk.com.

Let me know if you still have questions.