{"id":578,"date":"2024-09-25T07:05:09","date_gmt":"2024-09-25T06:05:09","guid":{"rendered":"https:\/\/moodytech.uk\/kb\/?post_type=knowledgebase&p=578"},"modified":"2024-09-25T07:17:20","modified_gmt":"2024-09-25T06:17:20","slug":"moody-project-report-vba","status":"publish","type":"knowledgebase","link":"https:\/\/moodytech.uk\/kb\/index\/excel\/moody-project-report-vba\/","title":{"rendered":"Moody Project Report (VBA)"},"content":{"rendered":"\r\nVisual Basic for Applications script to merge multiple .xlsx files generated from D365 > Project Management and Accounting > Enquiries and Reports > Moody Project Report\r\n

\r\nIn Excel, use Alt + F11 to invoke the Visual Basic for Applications tool, and use Alt + F8 to run the script.\r\n

\r\nThe script is made up of three parts:
\r\n\t
  • part 1 imports the multiple Excel files into separate worksheets in a single Excel file,<\/li>\r\n
  • part 2 renames each sheet based on the list of names found in the ‘List’ worksheet and subsequently moves the ‘Summary’ sheet from last to first position in the Workbook,<\/li>\r\n
  • and part 3 deletes the ‘List’ sheet<\/li>\r\n
    \r\nThe file can then be Saved As an ordinary XLSX file.\r\n

    \r\nPART 1 – MERGE FILES
    \r\nVBA_MergeFiles<\/a><\/strong>\r\n

    \r\nPART 2 – RENAME SHEETS
    \r\n    VBA_RenameSheets<\/a><\/strong>\r\n

    \r\nPART 3 – DELETE LIST SHEET
    \r\n    VBA_DeleteListSheet<\/a><\/strong>\r\n

    \r\nCOMBINED VBA SCRIPT
    \r\n    VBA_CombinedScripts<\/a><\/strong>\r\n

    \r\nDOWNLOAD XLSM FILE
    \r\n    https:\/\/www.moodydirect.com\/resources\/Master.xlsm<\/a><\/strong>\r\n\r\n
    \r\n
    \r\n\t