Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I am going insane trying to work this out...
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I am going insane trying to work this out...
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I am going insane trying to work this out...
Firstly many thanks for taking the time to reply and for explaining it in a easy way!! I have followed your instuctions to the letter and when i paste the 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 Syntax error and hi-lights the the row.. Range("A1").CurrentRegion.Sort Key1:=Range("F2"), Order1:=xlAscending, Is this something I am doing wrong? Thanks again -- 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I am going insane trying to work this out...
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" wrote: Firstly many thanks for taking the time to reply and for explaining it in a easy way!! I have followed your instuctions to the letter and when i paste the 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 Syntax error and hi-lights the the row.. Range("A1").CurrentRegion.Sort Key1:=Range("F2"), Order1:=xlAscending, Is this something I am doing wrong? Thanks again -- 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
I am going insane trying to work this out...
Hi Erin, I only just sorted that bit thanks for replying again, The code 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 changed the ("A1") bit to show the range? and If I wanted to add another column to the summary sheet to calulate the time worked, can this be added to the code? I already have the code i need for this purpose which is =ROUNDUP((E3-D3)*48,0)/2 Please ignore the cell references in this bit as its currently used on another workbook only I hope you don'y mind me asking Kind 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
I am going insane trying to work this out...
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. "Paul Cooke" wrote: Hi Erin, I only just sorted that bit thanks for replying again, The code 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 changed the ("A1") bit to show the range? and If I wanted to add another column to the summary sheet to calulate the time worked, can this be added to the code? I already have the code i need for this purpose which is =ROUNDUP((E3-D3)*48,0)/2 Please ignore the cell references in this bit as its currently used on another workbook only I hope you don'y mind me asking Kind 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A search for $ in a formula use to work now it does not work | Excel Discussion (Misc queries) | |||
Moving a sheet from one work book to another? | Excel Worksheet Functions | |||
My links no longer work . . . | Excel Discussion (Misc queries) | |||
How to get saved old saved work that was saved over? | Excel Discussion (Misc queries) | |||
Service work order | Charts and Charting in Excel |