Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help needed with Automating report (Excel 2003)

    Hi,

    I have a financial report that is prone to a lot of manual errors and therefore I want to automate it. The report needs to be generated from raw data (please see the tab Raw Data). The problem I am facing while automating this is that this format has been in existence for a while and my customer does not want any change in it. Each date in the column P of the Raw Data tab should be made into a new sheet with a date name (please see attachment) and have the format as in other sheets. Next, all the rows in the sheet should coincide with Column N of the Raw Data sheet (Task description). The columns in the new sheet should be the Initials of the names of resources working on the project - the Initials being present along with the names of resources in the Legend tab. There are some formulae in each of the "date" sheets..the right side sections under the heading "Resource-Wise Effort(Hrs)" need to come from Column T of Raw Data sheet for each resource. Each effort then needs to get multiplied with 110 if for that value the task code starts with 02 (see Column M of the Raw Data sheet) or 0 if the task code starts with 22 in Column M of the Raw Data sheet. Also, a validation is that each of the "date" sheets should only extract data from the Raw Data sheet for records marked "X" in Column W of the Raw Data sheet.

    Please help - This sample is just an extract of the raw data and the volume is very large.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help needed with Automating report (Excel 2003)

    Why does the sheet for 09282007 have an extra row (CAPERS DUE DILIGENCE REQUIREMENTS) compared with the others?

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed with Automating report (Excel 2003)

    Hans, I tried to add a task manually for which the Raw Data (basically a time-entry system dump) is yet to come. I think you could remove it - its not part of the model. It should only match the task descriptions in the Raw Data in the attachment/pick up the task descriptions from the Raw Data sheet if I were to add the dump data into the Raw Data sheet in that format. Can that be workable?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help needed with Automating report (Excel 2003)

    Will the set of task descriptions change over time? It would be a lot easier if it was a fixed list.

  5. #5
    New Lounger
    Join Date
    Feb 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed with Automating report (Excel 2003)

    Yes, Hans. That is the tricky part. Actually I could have gone with a Pivot Table but the customer wants it in the particular format that I shared since this format has been in use for some time now and they like it. Also, since they are still on 2003, the pivot table options are limited in terms of formats, unlike the 2007 format.

  6. #6
    New Lounger
    Join Date
    Feb 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed with Automating report (Excel 2003)

    Could it not take a unique list of all task descriptions for a particular date and then populate the data?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help needed with Automating report (Excel 2003)

    Pivot tables may not provide exactly the desired format, but they are a very convenient and efficient way to summarize the data in essentially the way the customer wants it. Excel 2003 may not have as many formatting options for pivot tables as Excel 2007, but it has a very decent range of choices. The amount of effort needed to reproduce the summaries without pivot tables is excessive, in my opinion. If you still want to go ahead with it, I wish you strength and good luck. You are very welcome to ask specific questions about details of the code if you get stuck, but I think that writing the entire application for you is beyond the scope of the Lounge.

  8. #8
    New Lounger
    Join Date
    Feb 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed with Automating report (Excel 2003)

    Sure, Hans. Could you just let me know a code to pick up dates from the Raw Data tab and create new sheets with those dates as tab labels?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help needed with Automating report (Excel 2003)

    See the thread starting at <post:=619,835>post 619,835</post:> and study the code provided there and in the other posts referred to. They show how to create worksheets based on the unique values in a column.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help needed with Automating report (Excel 2003)

    You might consider having the code create a pivot table to start with on a temporary sheet (this may do 80% of the work) and then copy/paste-special values the pivot table and manipulate this data table to format as desired. it could save much coding effort....

    Steve

  11. #11
    New Lounger
    Join Date
    Feb 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed with Automating report (Excel 2003)

    Hans, Seems like the attachments in the link you sent are not working anymore. If you/Legare could send me the VBA code, that would be great.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help needed with Automating report (Excel 2003)

    There are sample macros in <post:=380,563>post 380,563</post:>, <post:=413,718>post 413,718</post:>, <post:=471,071>post 471,071</post:> and <post:=619,863>post 619,863</post:>.

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed with Automating report (Excel 2003)

    The file in my <post:=620,062>post 620,062</post:> has been replaced and should now be available again. I do not still have the file from my other post in the same thread, I just kept the final version.
    Legare Coleman

  14. #14
    New Lounger
    Join Date
    Feb 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed with Automating report (Excel 2003)

    Hi Legare,

    Thanks for the code. I could use this to some success - the sheets are getting generated. However, there is a circular reference getting generated. What I am trying to do is using one macro to convert the date fields under the "PA Date" header to text and get them to column A using a macro. Then using your macro generating sheets. Could you also tell me how to generate a pivot table like data, specific to a particular date (pertaining to the specific date tab) within each of the date sheets? I need Task Description as a row field and Material description as a column field, with Sum of Billable Quantity as data field. Actually, while I can make a macro independently, if you could help me create a pivot table specific to a date (matching the date of the sheet label), that would do my job.
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help needed with Automating report (Excel 2003)

    Here is a macro that will generate sheets with a pivot table for each date.
    Before running it, you must restore the field names in A1 and B1, they seem to have been lost.

    Sub CreatePivot()
    Dim pc As PivotCache
    Dim pt As PivotTable
    Set pc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
    SourceData:=Worksheets("Raw Data").Range("A1").CurrentRegion)
    Set pt = pc.CreatePivotTable(TableDestination:="", TableName:="MyPvt")
    pt.AddFields _
    RowFields:="Task description", _
    ColumnFields:="Material description", _
    PageFields:="PA date"
    With pt.PivotFields("Billable quantity")
    .Orientation = xlDataField
    .Caption = "Sum of Billable quantity"
    End With
    pt.ShowPages PageField:="PA date"
    End Sub

Posting Permissions

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