+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Forum Contributor
    Join Date
    12-01-2005
    Location
    Somerset, England
    MS-Off Ver
    Office 2010 Pro
    Posts
    138

    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

  2. #2
    Erin Searfoss
    Guest

    RE: 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. #3
    Forum Contributor
    Join Date
    12-01-2005
    Location
    Somerset, England
    MS-Off Ver
    Office 2010 Pro
    Posts
    138

    Hi Erin

    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

  4. #4
    Erin Searfoss
    Guest

    Re: 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. #5
    Forum Contributor
    Join Date
    12-01-2005
    Location
    Somerset, England
    MS-Off Ver
    Office 2010 Pro
    Posts
    138

    Hi Erin

    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

  6. #6
    Erin Searfoss
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1