+ Reply to Thread
Results 1 to 18 of 18

Macro- Requirement for report generation

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    35

    Macro- Requirement for report generation

    Hi All,

    Im in need of a report generation.

    The data to be present on this report are stored in a folder say : "C:\Test"

    Over here there are about 100 workbooks.
    The requirements are as below:
    1. The macro should browse these 100 workbooks.
    2. Go to Sheet 1 of each workbook - Sheet Name : "Progress Report"
    3. Collect the details required.
    4. Copy and paste the details collected on a report format.

    Conditions:
    1. The project name, project manager and project number will always appear on the columns mentioned in the attachment.
    2. The value financials indicated in sheet 1 of the attachment may be present at row number 17 sometimes, row number 18 or even row number 23.
    3. Macro should search for the string Financials highlighted in bold and copy the subsequent two values adjacent to it.

    The attachment on sheet gives the data to be copied
    Sheet 2 gives the view on which the report should look.

    Please refer the attachment.
    Attached Files Attached Files

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro- Requirement for report generation

    Try this code
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose Tools | Macro | Macros
    Select a macro in the list, and click the Run button
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    09-27-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Macro- Requirement for report generation

    Wow! You are great it really works the way I want.

    Just a small issue. It opens all the workbooks from the folder and the macro run is complete without the break.

    At the end of macro run I can just see the data of the last workbook present on the current workbook on which the macro was run.

    Could you please solve this issue for me ???

    Regards,
    Lakshmi

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro- Requirement for report generation

    It opens all the workbooks from the folder and the macro run is complete without the break.
    What do you mean by this? It opens up each workbook and does the copy paste and then closes them.

    At the end of macro run I can just see the data of the last workbook present on the current workbook on which the macro was run.
    You should see the data for all the files that were present in the folder.

  5. #5
    Registered User
    Join Date
    09-27-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Macro- Requirement for report generation

    Macro Run Report.xlsHi Arlette,

    Please find the macro generated report. You can undertand the issue.

    If this gets fixed it will be of a great benefit for me.

    True Regards,
    Lakshmi

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro- Requirement for report generation

    Will column A of your files be empty in most cases? From your data, it looks like column B will be the one which will always have data in it. If so, i will change column A to column B in your code -
    Updated code here -
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    09-27-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Macro- Requirement for report generation

    It works perfectly well No issues/ errors.

    Thanks a lot

    Regards,
    Lakshmi

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro- Requirement for report generation

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

  9. #9
    Registered User
    Join Date
    09-27-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Macro- Requirement for report generation

    I have another macro requirement.... to this
    From the same path

    I have two generate two additional reports.

    1. Greater than 15 report
    2. Non green status report

    Requirement:

    1. The macro should browse all workbooks at the path "C:\Test"
    2. Access the risks worksheet from all workbooks
    3. Copy all the risks whose status is open as per the column "I" of the worksheet for which the impact is greater than the numerical value 15 (This value is present at column D of the risks worksheet).
    4. Before doing this copy the macro has to check the current week Rag status of each project which is at column E2 of the workbook under the "Progress Report" worksheet.
    if the RAG status is green but it has a risk impact >=15 then this open status risk with impact value greater than equal to 15 should be copied onto the report.

    Can u help me on this also please

    Regards,
    Lakshmi

  10. #10
    Registered User
    Join Date
    09-27-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Macro- Requirement for report generation

    Please let me know if I can mark this solved and post the above thread as a new ones....

    Regards!!!

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro- Requirement for report generation

    Thats what i was going to tell you. If its a different question, mark this as solved, and open a new thread.

  12. #12
    Registered User
    Join Date
    09-27-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Macro- Requirement for report generation

    I have now marked this as solved...

    I will post the above as a new thread...

    Please solve this one indeed...
    Thanks in advance!!!

    Lakshmi

  13. #13
    Registered User
    Join Date
    09-27-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Macro- Requirement for report generation

    Hi Arlette


    Just a small help. The macro runs exceptionally well now. Just that it misses to capture the details from 5 workbooks.

    I m not able to find the reason.

    Any help please


    Regards,
    Lakshmi

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro- Requirement for report generation

    Hi Lakshmi,

    I had unsubscribed from this thread, and hence didnt see your reply.

    You said it misses 5 workbooks. What is the file extension of these 5?

  15. #15
    Registered User
    Join Date
    09-27-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Macro- Requirement for report generation

    Its an ".xls" file only but it has got some links enabled with them. While I open these excel workbooks they ask if it is "Continue", "Dont Update" or "Update"

    Regards,
    Lakshmi

  16. #16
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro- Requirement for report generation

    So if you want you can disable the popups completely, but am not sure if that will still open those files to copy the data.

    You can try with application.displayalerts=false (copy it just after the DIM statements) and application.displayalerts=true before the End Sub.

  17. #17
    Registered User
    Join Date
    09-27-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Macro- Requirement for report generation

    I want to use the same code to extract another report. Iuse the code below

    Option Explicit

    Sub cons_data()

    Dim Master As Workbook, sourceBook As Workbook
    Dim sourceData As Worksheet
    Dim CurrentFileName As String, myPath As String
    Dim lastrow As Long, lrow As Long, i As Long

    Application.ScreenUpdating = False

    'The folder containing the files to be recap'd
    myPath = "C:\Test"

    'Finds the name of the first file of type .xls in the current directory
    CurrentFileName = Dir(myPath & "\*.xls")

    'Create a workbook for the recap report
    Set Master = ThisWorkbook
    If Master.Worksheets(1).Range("A1").Value = "" Then _
    Master.Worksheets(1).Range("A1:E1").Value = Split("Project Name, Project Number, Project Manager, Financial Rag, Financial Comments", ",")

    Do
    Workbooks.Open (myPath & "\" & CurrentFileName)
    Set sourceBook = Workbooks(CurrentFileName)
    Set sourceData = sourceBook.Worksheets("Risks")

    With sourceData

    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 1 To lrow
    If .Range("D" & i).Value = "Open" Then
    .Range("A" & i &lrow).EntireRow.Copy
    Master.Worksheets(1).Range("D" & lastrow + 1).PasteSpecial Paste:=xlPasteValues
    Exit For
    End If
    Next i

    End With

    sourceBook.Close

    'Calling DIR w/o argument finds the next .xlsx file within the current directory.
    CurrentFileName = Dir()
    Loop While CurrentFileName <> ""

    MsgBox "Consolidation complete"

    Application.ScreenUpdating = True

    End Sub

    But Im getting an error on this....

    Can you spot why?

    Requirement:

    1. Browse the risks worksheet from the "C:\Test" folder
    2. Access all the workbooks.
    3. Copy all risks whose status is "Open" .
    4. Paste them on the report.


    Regards,
    Lakshmi

  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro- Requirement for report generation

    Lakshmi,

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

    Please put code tags in your previous post itself. Do not create a new post.

    Also, is this question related to this thread? If not, please create a new thread.

+ 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