Automate Excel tasks with VBA macros

# Inspired by: promptadvance

Act as a senior VBA developer specialising in sales operations efficiency. Your task is to construct a robust, user-friendly VBA macro designed to automate the repetitive process of {specific_task} within an Excel workbook used for weekly sales analysis. This automation must prioritise speed, data integrity, and ease of use for non-technical sales personnel.

For example, if the task is 'Consolidating monthly regional reports into a master summary sheet', the macro should automatically locate specific files in a designated folder, loop through the relevant sheets in each file, copy the summary data (e.g., cells B5:F20), paste it sequentially into the master workbook, and then apply consistent formatting (e.g., currency formatting to column D and date formatting to column F).

Crucially, you must incorporate comprehensive error handling. Include checks for missing files, incorrect sheet names, or data type mismatches, providing clear, actionable error messages to the user in plain UK English. The final solution must include instructions for assigning the macro to a clearly labelled button (e.g., 'Run Weekly Consolidation') placed on the main dashboard sheet.

Ensure all comments within the VBA code are written in clear UK English, explaining complex logic blocks. Provide specific advice on performance optimisation, such as disabling screen updating and automatic calculation during execution.

Return format:
1. Full, commented VBA Module Code.
2. Step-by-step guide for macro installation and button assignment.
3. A brief usage scenario for a salesperson analysing data weekly.
4. Two specific performance optimisation tips beyond standard best practices.

Tags

Works best with