Creative Blog

Étiquette : vba

  • Automate the demand forecasting process in Excel using VBA

    Automating the demand forecasting process in Excel using VBA can be a complex project, but here’s a detailed example to get you started. We’ll create a simple demand forecasting model using VBA to automate data import, model creation, and displaying results.

    Main Steps:

    1. Import Historical Sales Data into Excel.
    2. Prepare Data for modeling (cleaning and structuring the data).
    3. Create a simple forecasting model (e.g., moving average or linear regression).
    4. Display forecasts in a table or graph.

    Below is an example of automating a demand forecasting process using a moving average model. We’ll use VBA to automate these steps.

    VBA Code Example for Demand Forecasting

    1. Create a macro to import data

    Let’s assume you have a CSV file with historical sales data. Here is the VBA code to import that data into an Excel worksheet.

    Sub ImportSalesData()
    Dim ws As Worksheet
    Dim filePath As String
    Dim lastRow As Long   
    ' Set the target worksheet where data will be imported
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Prompt the user to select a CSV file
    filePath = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Select a CSV file")   
    If filePath = "False" Then Exit Sub ' Exit if the user cancels   
    ' Import the CSV file into the active worksheet
    With ws.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=ws.Range("A1"))
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With  
    ' Find the last row of the imported data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    MsgBox "Data imported up to row " & lastRow
End Sub
    1. Create a macro to calculate demand forecasts

    Let’s assume your sales data is in column A, and you want to calculate the forecast using a 3-period moving average. Here is the code to calculate that:

    Sub CalculateDemandForecasts()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim period As Integer
    Dim i As Long
    Dim sum As Double
    Dim forecast As Double   
    ' Set the target worksheet where data is located
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Set the period for the moving average (here 3 months)
    period = 3   
    ' Add a header for the forecasted demand
    ws.Cells(1, 2).Value = "Demand Forecast"   
    ' Calculate the moving average for the last 3 months
    For i = period To lastRow
        sum = 0
        ' Sum the last 3 months
        For j = 0 To period - 1
            sum = sum + ws.Cells(i - j, 1).Value
        Next j
        ' Calculate the moving average
        forecast = sum / period
        ' Display the forecast in column B
        ws.Cells(i, 2).Value = forecast
    Next i   
    MsgBox "Forecast calculation completed!"
End Sub
    1. Create a macro to plot a chart

    After calculating the forecasts in column B, you can add a chart to visualize the actual demand vs the forecasted demand. Here is a code to create a simple line chart:

    Sub CreateForecastChart()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim chartObj As ChartObject   
    ' Set the target worksheet where data is located
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Create a chart from the sales and forecast data
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
    With chartObj.Chart
        .SetSourceData Source:=ws.Range("A1:B" & lastRow)
        .ChartType = xlLine ' Line chart type
        .HasTitle = True
        .ChartTitle.Text = "Sales and Demand Forecast"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Period"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Quantity"
    End With   
    MsgBox "Chart created successfully!"
End Sub

    Explanation of the Code:

    1. ImportSalesData: This macro imports sales data from a CSV file into Excel. It prompts the user to select the file and then imports it into the active worksheet.
    2. CalculateDemandForecasts: This macro calculates the demand forecast using a 3-period moving average. It sums the sales of the last 3 periods and then calculates the average to generate the forecast. The forecasts are placed in column B.
    3. CreateForecastChart: This macro creates a line chart comparing actual sales data (from column A) and the demand forecast (from column B). The chart is created on the same worksheet, and it provides a visual representation of the sales and forecast trends.

    Customization:

    • You can adjust the period for the moving average by changing the period variable in the CalculateDemandForecasts macro.
    • If you want to use a more sophisticated forecasting model (e.g., linear regression or ARIMA), you would need to integrate additional functions in VBA or call external models via an R or Python library.

    Conclusion:

    This example demonstrates how to automate a basic demand forecasting process using VBA in Excel. You can adapt the model to suit your needs by adjusting the forecasting method, period, or adding more advanced statistical models to improve the accuracy of your fo

  • Automating demand planning in Excel using VBA

    Automating demand planning in Excel using VBA can streamline tasks such as analyzing past demand, forecasting future needs, and managing stock based on historical data. The goal is to optimize resources and ensure that products or services are available according to demand forecasts.

    In this example, I’ll guide you through a process that includes the following steps:

    1. Collecting historical demand data.
    2. Calculating demand forecasts.
    3. Analyzing the gap between forecasted demand and available stock.
    4. Automating the update of forecasts in a table.

    Step 1: Organize Your Data in Excel

    Ensure that your historical demand data is organized in a worksheet. For example, here’s a simple layout:

    Date Actual Demand
    01/01/2023 100
    02/01/2023 120
    03/01/2023 110
    04/01/2023 130

    In this example, the « Date » column represents the date of the demand, and the « Actual Demand » column represents the quantity demanded for each day.

    Step 2: The VBA Code to Automate Demand Planning

    Here’s the detailed VBA code that automates this process:

    Sub AutomateDemandPlanning()
    Dim wsData As Worksheet
    Dim wsForecast As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentDate As Date
    Dim actualDemand As Double
    Dim forecastDemand As Double
    Dim avgDemand As Double
    Dim demandGap As Double
    Dim availableStock As Double
    Dim reorderThreshold As Double
    Dim planningColumn As Range   
    ' Define the worksheets
    Set wsData = ThisWorkbook.Sheets("DemandHistory") ' Historical demand data
    Set wsForecast = ThisWorkbook.Sheets("DemandForecast") ' Forecast data   
    ' Find the last row of data in DemandHistory
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row   
    ' Initialize the planning column in the Forecast sheet
    Set planningColumn = wsForecast.Range("B2:B" & lastRow)   
    ' Calculate the average demand for forecasting
    Dim totalDemand As Double
    totalDemand = 0
    For i = 2 To lastRow
        totalDemand = totalDemand + wsData.Cells(i, 2).Value
    Next i
    avgDemand = totalDemand / (lastRow - 1)   
    ' Define the reorder threshold (based on stock policy)
    reorderThreshold = 200 ' Value to adjust based on your business   
    ' Fill in the forecast data into the "DemandForecast" sheet
    For i = 2 To lastRow
        ' Get the date and actual demand
        currentDate = wsData.Cells(i, 1).Value
        actualDemand = wsData.Cells(i, 2).Value       
        ' Calculate the forecast demand (using the average for simplicity)
        forecastDemand = avgDemand       
        ' Calculate the gap between actual and forecast demand
        demandGap = actualDemand - forecastDemand       
        ' Get the available stock (assuming this is in column C of the Forecast sheet)
        availableStock = wsForecast.Cells(i, 3).Value       
        ' Add forecast, demand gap, and stock status to the Forecast sheet
        wsForecast.Cells(i, 1).Value = currentDate ' Date
        wsForecast.Cells(i, 2).Value = forecastDemand ' Forecasted demand
        wsForecast.Cells(i, 3).Value = availableStock ' Available stock
        wsForecast.Cells(i, 4).Value = demandGap ' Gap between actual and forecast demand       
        ' Check if stock is insufficient and flag for reorder
        If availableStock < reorderThreshold Then
            wsForecast.Cells(i, 5).Value = "Reorder Required"
        Else
            wsForecast.Cells(i, 5).Value = "Sufficient"
        End If
    Next i   
    MsgBox "Demand planning automated successfully!", vbInformation
End Sub

    Detailed Explanation of the Code:

    1. Define Variables and Worksheets:
      • wsData: The worksheet containing historical demand data.
      • wsForecast: The worksheet where forecasted demand will be stored.
      • lastRow: Identifies the last row of data in the « DemandHistory » sheet.
    2. Calculate the Average Historical Demand:
      • This average serves as the basis for the demand forecast.
    3. Fill the Forecast Sheet:
      • The code populates the « DemandForecast » sheet with the following information:
        • Date: The date of the demand.
        • Forecasted Demand: The forecast value (based on the average).
        • Actual Demand: The actual historical demand.
        • Demand Gap: The difference between actual and forecasted demand.
        • Available Stock: The stock on hand as of the forecast date (assumed to be in the forecast sheet).
        • Reorder Required: Indicates whether stock is below a reorder threshold.
    4. Reorder Alert:
      • The code checks if the available stock is below the reorder threshold and flags it as « Reorder Required » if necessary.

    Step 3: Using the Code in Excel

    1. Open Excel and create two sheets:
      • DemandHistory: Contains the historical demand data.
      • DemandForecast: Will contain the forecast results, including planning and reorder alerts.
    2. Open the VBA editor (Alt + F11), create a new module, and paste the code into it.
    3. To run the macro, go to Tools > Macro > Macros, select AutomateDemandPlanning, and click Run.

    Customization of the Code

    • Forecasting Method: This code uses a simple average of past demand for forecasting. You can replace it with more advanced techniques such as exponential smoothing, regression analysis, or more detailed forecasting models.
    • Reorder Threshold: Modify the reorder threshold value based on your specific business requirements.

    This process will automate a part of the demand planning work, but you can complement it with more in-depth analysis and inventory management tools to optimize your forecasts.

     

  • Automate decision-making processes in Excel using VBA

    Automating decision-making processes in Excel using VBA (Visual Basic for Applications) can be a powerful way to streamline repetitive tasks. This type of automation can include processes for selecting, analyzing, and executing actions based on predefined criteria. Below is a detailed example of VBA code that can automate a decision-making process.

    Scenario

    Let’s imagine a scenario where we have a list of projects, and for each project, there are several criteria such as cost, risk, and impact. The final decision of whether to « accept » or « reject » the project will be based on these criteria. If the cost is below a certain value, the risk is low, and the impact is high, the project will be accepted. Otherwise, it will be rejected.

    Steps:

    1. Create a worksheet with the data.
    2. Write the VBA code to automate the decision.
    3. Display a message or notification of the decision.
    1. Create the Worksheet

    Imagine a worksheet called Projects with the following columns:

    A B C D E
    Project Cost (€) Risk Impact Decision
    Project 1 100000 Low High ?
    Project 2 150000 High Medium ?
    Project 3 50000 Medium High ?
    … … … … …

    In this example:

    • Column A: Project name
    • Column B: Cost in €
    • Column C: Risk level (Low, Medium, High)
    • Column D: Impact level (Low, Medium, High)
    • Column E: Final decision (Accept or Reject), which will be automated using VBA.
    1. VBA Code to Automate the Decision-Making Process

    Here is the VBA code that analyzes each project based on the cost, risk, and impact criteria, and then makes the corresponding decision.

    VBA Code

    Sub AutomateDecision()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cost As Double
    Dim risk As String
    Dim impact As String
    Dim decision As String   
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Projects")   
    ' Find the last row of data (based on column A)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Loop through each project
    For i = 2 To lastRow ' Start from row 2 to skip the headers
        cost = ws.Cells(i, 2).Value ' Project cost
        risk = ws.Cells(i, 3).Value ' Project risk
        impact = ws.Cells(i, 4).Value ' Project impact       
        ' Decision-making logic
        If cost < 120000 And risk = "Low" And impact = "High" Then
            decision = "Accept"
        ElseIf cost >= 120000 And risk = "High" Then
            decision = "Reject"
        ElseIf cost < 80000 And impact = "Medium" Then
            decision = "Reject"
        Else
            decision = "Accept"
        End If       
        ' Assign the decision to column E
        ws.Cells(i, 5).Value = decision
    Next i  
    ' Message box when the process is completed
    MsgBox "The decision process has been completed.", vbInformation, "Automation Completed"
End Sub
    1. Explanation of the Code

    Variable Declarations

    Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim cost As Double
Dim risk As String
Dim impact As String
Dim decision As String
    • ws: Reference to the worksheet containing the data.
    • lastRow: The last row used in the worksheet to loop through all the projects.
    • i: Loop index for iterating through the rows.
    • cost, risk, impact: Variables used to store the values of each project’s criteria.
    • decision: Variable to store the final decision for each project.

    Find the Last Row

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    • This line finds the last used row in column A (assuming that each project has a name in this column).

    Loop Through Projects

    For i = 2 To lastRow
    • We start at row 2 to skip the headers and loop through each project.

    Read Criteria Values

    cost = ws.Cells(i, 2).Value
risk = ws.Cells(i, 3).Value
impact = ws.Cells(i, 4).Value
    • These lines retrieve the values of cost, risk, and impact for each project.

    Decision-Making Logic

    If cost < 120000 And risk = "Low" And impact = "High" Then
    decision = "Accept"
ElseIf cost >= 120000 And risk = "High" Then
    decision = "Reject"
ElseIf cost < 80000 And impact = "Medium" Then
    decision = "Reject"
Else
    decision = "Accept"
End If
    • This section contains the decision-making logic based on the criteria. For example:
      • If the cost is less than 120,000 €, the risk is low, and the impact is high, the project is accepted.
      • If the cost is above or equal to 120,000 € and the risk is high, the project is rejected.
      • Additional conditions can be added for more flexibility.

    Assign the Decision to the Worksheet

    ws.Cells(i, 5).Value = decision
    • This line places the decision in column E (the decision column) for each project.

    Completion Message

    MsgBox "The decision process has been completed.", vbInformation, "Automation Completed"
    • A message box pops up to inform the user that the automation process has finished.
    1. Running the Code

    Open Excel and press Alt + F11 to open the VBA editor.

    In the editor, go to Insert > Module to create a new module.

    Copy and paste the VBA code above into the module.

    Close the VBA editor and return to Excel.

    Press Alt + F8, select the AutomateDecision macro, and click Run.

    Conclusion

    This VBA code automates the decision-making process based on predefined criteria in an Excel sheet. You can customize the code to fit other decision-making scenarios depending on your project’s requirements. The code can be expanded with additional conditions or more complex logic as needed

  • Automate database queries using VBA in Excel

    Automating database queries using VBA in Excel can save time and reduce manual errors. The goal here is to query a database using SQL, execute the query, and retrieve the results into an Excel sheet.

    Below is a detailed VBA code example that connects to a SQL database (like SQL Server, MySQL, or Access), queries it, and retrieves the data into an Excel sheet. We will use ADO (ActiveX Data Objects) for the database connection.

    Example Steps

    1. Connect to the database.
    2. Execute an SQL query.
    3. Retrieve the results into an Excel sheet.
    4. Handle errors.

    Detailed VBA Code

    Sub QueryDatabase()
    ' Declare variables
    Dim Conn As Object ' Connection object to the database
    Dim Rs As Object ' Recordset object to store the query results
    Dim StrSQL As String ' SQL query to execute
    Dim Row As Long ' Variable to determine which row in Excel to paste the result
    Dim ConnString As String ' Connection string for the database
    ' Connection string (for SQL Server)
    ConnString = "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password"
    ' Create ADO Connection and Recordset objects
    Set Conn = CreateObject("ADODB.Connection")
    Set Rs = CreateObject("ADODB.Recordset")
    ' Attempt to connect to the database
    On Error GoTo ConnectionError
    Conn.Open ConnString
    On Error GoTo 0 ' Reset error handling
    ' Define the SQL query
    StrSQL = "SELECT * FROM MyTable" ' Replace with your own SQL query
    ' Execute the query
    Rs.Open StrSQL, Conn
    ' Check if the recordset contains any results
    If Not Rs.EOF Then
        ' Start pasting results from the second row (assuming row 1 contains headers)
        Row = 2
        ' Loop through the recordset and paste the results into Excel
        Do While Not Rs.EOF
            ' Fill each cell in the row
            For i = 0 To Rs.Fields.Count - 1
                Sheets("Sheet1").Cells(Row, i + 1).Value = Rs.Fields(i).Value
            Next i
            Row = Row + 1 ' Move to the next row
            Rs.MoveNext ' Move to the next record
        Loop
    Else
        MsgBox "No results found", vbInformation
    End If
    ' Close the Recordset and the Connection
    Rs.Close
    Conn.Close
    ' Release the objects
    Set Rs = Nothing
    Set Conn = Nothing
    MsgBox "Data retrieved successfully!", vbInformation
    Exit Sub
ConnectionError:
    MsgBox "Database connection error: " & Err.Description, vbCritical
    Set Rs = Nothing
    Set Conn = Nothing
End Sub

    Explanation of the Code

    1. Declared Variables:
      • Conn: The object representing the connection to the database.
      • Rs: The Recordset object that stores the query results.
      • StrSQL: The SQL query to execute.
      • Row: The row number in Excel where the results will be pasted.
      • ConnString: The connection string containing the information needed to connect to the database (server, database name, user credentials).
    2. Connecting to the Database:
      • We use CreateObject(« ADODB.Connection ») to create a connection object.
      • The connection is established using Conn.Open ConnString, with the connection string containing the necessary details like server name, database name, username, and password.
    3. Executing the SQL Query:
      • The SQL query is defined in the StrSQL variable. You can modify this query to fit your specific needs, such as filtering or selecting specific columns.
      • Rs.Open StrSQL, Conn executes the SQL query and stores the results in the Recordset (Rs).
    4. Processing Results in Excel:
      • If results are returned (Recordset is not empty), the code loops through each record and pastes the values into Excel starting from row 2 (assuming row 1 contains headers).
      • The columns in the Recordset are iterated, and their values are pasted into the corresponding cells in the Excel sheet.
    5. Closing the Connection:
      • After the data is retrieved, the Recordset and connection are closed using Rs.Close and Conn.Close.
      • The objects are released by setting them to Nothing to avoid memory leaks.
    6. Error Handling:
      • A simple error handler (On Error GoTo ConnectionError) is used to capture any connection errors and display an error message if the connection fails.

    Customization

    • Connection String: Modify the connection string (ConnString) to match your database type. For example, for MySQL, you might use a MySQL OLE DB provider.
    • SQL Query: Change the StrSQL variable to match the SQL query that you want to run. You can filter data, select specific columns, or join tables.
    • Sheet Name: Ensure the sheet name (Sheet1) matches the sheet where you want to paste the results, or adjust it as needed.

    Conclusion

    This VBA code serves as a solid foundation to automate querying a database and importing the results into Excel. It can be customized further for advanced use cases, such as exporting data, scheduling queries, or applying additional filters to the query.

     

  • Automate the process of integrating databases into Excel using VBA

    Automating the process of integrating databases into Excel using VBA (Visual Basic for Applications) is an excellent way to save time and improve efficiency. Generally, integrating databases into Excel can be done via connections to databases like SQL Server, MySQL, or even Access. This process involves extracting data, processing it, and displaying it in an Excel worksheet.

    In this example, I’ll explain how to create a VBA script that automates the extraction of data from a SQL Server database into Excel, performs some simple transformations (e.g., calculations or adding filters), and inserts the data into a worksheet.

    Prerequisites:

    1. You must have a database from which you want to extract data (for example, an SQL Server database).
    2. You need to have a reference to « Microsoft ActiveX Data Objects » in the VBA editor in Excel.
      • To do this, open the VBA editor (press Alt + F11), then go to Tools > References and check « Microsoft ActiveX Data Objects x.x Library. »

    Detailed VBA Code for Automating Data Integration

    Here is an example of detailed VBA code that connects to a SQL Server database, retrieves data, inserts it into an Excel worksheet, and performs some basic processing (e.g., calculating a column or adding filters):

    Sub ImportDataFromDatabase()
    ' Declare necessary variables
    Dim Conn As Object
    Dim Recordset As Object
    Dim SQLQuery As String
    Dim ws As Worksheet
    Dim i As Integer   
    ' Create a new worksheet
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "Imported Data"   
    ' Define the connection string for the SQL Server database
    Dim ConnString As String
    ConnString = "Provider=SQLOLEDB;Data Source=YOUR_SERVER;Initial Catalog=YOUR_DB;User ID=YOUR_USER;Password=YOUR_PASSWORD;"   
    ' Create the connection object
    Set Conn = CreateObject("ADODB.Connection")
    Conn.Open ConnString
    ' Define the SQL query to retrieve data
    SQLQuery = "SELECT Column1, Column2, Column3 FROM YourTable"   
    ' Execute the query and store the result in a Recordset
    Set Recordset = CreateObject("ADODB.Recordset")
    Recordset.Open SQLQuery, Conn   
    ' Copy the data into the worksheet
    ' Column headers
    For i = 0 To Recordset.Fields.Count - 1
        ws.Cells(1, i + 1).Value = Recordset.Fields(i).Name
    Next i   
    ' Insert the data row by row into the worksheet
    ws.Cells(2, 1).CopyFromRecordset Recordset   
    ' Apply a table format (optional)
    ws.Range("A1").CurrentRegion.TableStyle = "TableStyleLight9"   
    ' Close the connection and Recordset
    Recordset.Close
    Conn.Close  
    ' Release the objects
    Set Recordset = Nothing
    Set Conn = Nothing  
    ' Alert that the import is complete
    MsgBox "Data import is complete!", vbInformation
End Sub

    Explanation of the Code:

    1. Variable Declarations:
      • Conn: A variable for the connection to the database.
      • Recordset: A variable to store the data retrieved from the database.
      • SQLQuery: A string containing the SQL query.
      • ws: The worksheet where the data will be inserted.
    2. Connection String (ConnString):
      • The connection string contains information to connect to the SQL Server database. It includes the server name, database name, and user credentials (username and password).
    3. Database Connection:
      • The Conn object is created and opened using the specified connection string.
    4. Executing the SQL Query:
      • The SQL query (SQLQuery) is executed via the Recordset object. The result is returned and stored in Recordset.
    5. Inserting Data into Excel:
      • The column names are copied into the first row of the Excel worksheet.
      • Then, the data extracted from the Recordset is inserted starting from row 2 using the CopyFromRecordset method.
    6. Formatting the Table:
      • The code applies a table style to the imported data range in Excel to improve readability and organization.
    7. Closing Objects:
      • The Recordset and Conn objects are closed and released to free up system resources.
    8. Completion Message:
      • A message box is displayed to confirm that the import process is complete.

    Possible Enhancements:

    • Filtering or Transforming Data: You can add steps to filter or transform the data before inserting it into Excel (e.g., summing a column or changing date formats).
    • Error Handling: Use error handlers such as On Error GoTo to manage connection or query execution errors.
    • Scheduled Automation: If you want this process to run automatically at specific times (e.g., every day at a certain hour), you can schedule the script using Windows Task Scheduler or have it run automatically when the Excel file is opened.

    Example Code for Filtering Data Before Insertion:

    ' Filter the data by adding a condition to the SQL query
SQLQuery = "SELECT Column1, Column2 FROM YourTable WHERE Condition = 'Value'"

    Conclusion:

    This VBA code automates the process of importing data from a SQL Server database into Excel. It provides a simple method to extract information, import it into a worksheet, and perform actions like transformations or filtering before displaying it. This solution is customizable to work with other databases or specific requirements.

     

  • Automate data visualization processes in Excel using VBA

    This code will help you automate the creation of charts, apply conditional formatting, and add interactivity to your data visualizations.

    Scenario

    Let’s assume you have a dataset in an Excel sheet, and you want to create a chart based on this data while automating formatting. The following VBA code shows how to automate chart creation from data and apply basic formatting.

    Example VBA Code for Automating Data Visualization

    1. Automating Chart Creation from Data
    2. Applying Conditional Formatting
    3. Adding Interactivity (e.g., filters)

    Detailed VBA Code with Explanation

    Sub AutomateVisualization()
    ' Declare variables
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim chartObj As ChartObject
    Dim dataRange As Range   
    ' Reference to the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Ensure the sheet name is correct   
    ' Find the last row of data (Column A)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Define the data range to visualize (e.g., Columns A to C)
    Set dataRange = ws.Range("A1:C" & lastRow)  ' Adjust the columns as needed   
    ' Create a chart from the data
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
    With chartObj.Chart
        .SetSourceData Source:=dataRange  ' Set the data source for the chart
        .ChartType = xlColumnClustered  ' Chart type (clustered column chart)
        .HasTitle = True
        .ChartTitle.Text = "Data Visualization"  ' Title of the chart
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Categories"  ' X-axis title
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Values"  ' Y-axis title
    End With 
    ' Apply conditional formatting to the data range
    With dataRange
        .FormatConditions.Delete  ' Delete any previous conditional formatting
        ' Apply conditional formatting for values greater than 100
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="100"
        .FormatConditions(1).Interior.Color = RGB(255, 0, 0)  ' Red color for values greater than 100
    End With
    ' Add an autofilter to the data range
    ws.Range("A1:C1").AutoFilter
    ' Display a confirmation message
    MsgBox "The chart has been created and the formatting applied successfully!", vbInformation
End Sub

    Code Explanation

    1. Declaring Variables
      • ws: A reference to the active worksheet where the data is located.
      • lastRow: This variable determines the last row of data in column A. It prevents hardcoding the row numbers.
      • dataRange: The range of data that will be used for creating the chart.
    2. Creating the Chart
      • ChartObjects.Add: This creates a new chart object in the worksheet.
      • SetSourceData: This sets the source of the chart data.
      • ChartType: Defines the chart type, in this case, a clustered column chart (xlColumnClustered).
      • Chart Titles: Customizes the chart title, and the titles for the X and Y axes.
    3. Applying Conditional Formatting
      • FormatConditions.Delete: Deletes any existing conditional formatting.
      • FormatConditions.Add: Adds new conditional formatting. In this case, it highlights cells with values greater than 100 by coloring them red (RGB(255, 0, 0)).
    4. Adding Autofilter
      • AutoFilter: Adds a filter to the header row (Row 1) so you can easily sort and filter data.
    5. Confirmation Message
      • MsgBox: Displays a message box to the user confirming that the chart was created and formatting applied successfully.

    Step-by-Step Guide to Run the Code

    1. Open Excel.
    2. Press Alt + F11 to open the VBA editor.
    3. In the editor, click Insert > Module to create a new module.
    4. Copy and paste the code above into this module.
    5. Close the VBA editor.
    6. Go back to Excel, press Alt + F8, select AutomateVisualization, and click Run.

    What the Code Does

    • Creates a Chart: The code generates a chart automatically based on the dataset, which helps visualize the data without manual steps.
    • Conditional Formatting: It highlights specific data points (e.g., values greater than 100) for better analysis and presentation.
    • AutoFilter: Adds an auto-filter to the column headers, allowing easy sorting and filtering of the data.
    • Confirmation Message: A message box appears once the process is complete, providing confirmation.

    Customization

    • You can easily modify the type of chart by changing ChartType = xlColumnClustered to other types like xlLine for line charts or xlPie for pie charts.
    • Adjust the dataRange to include more or fewer columns, depending on your dataset.
    • You can add more complex conditional formatting based on your needs, such as highlighting values below a threshold or using color scales.

    Conclusion

    This VBA code automates the process of creating a chart, applying conditional formatting, and adding interactivity (filters) in Excel. By automating these tasks, you save time, reduce errors, and make your data visualizations more dynamic and easier to analyze. You can further customize the code based on your specific needs, such as adding more formatting options, using different chart types, or modifying the data range.

     

  • Automate data validation rules in Excel using VBA

    To automate data validation rules in Excel using VBA, you can create macros that apply specific validation rules to certain cells or ranges. Below is a detailed example that explains how to automate data validation with VBA, including simple rules like number validation, date validation, and dropdown lists.

    VBA Code Objectives:

    1. Apply data validation to allow only numbers in a cell.
    2. Apply data validation to allow only dates in a range of cells.
    3. Create a dropdown list with predefined options.
    4. Add custom error messages when validation rules are not met.

    Step-by-step:

    1. Validation for Allowing Only Numbers

    This example applies data validation to cell A1 to allow only numbers between 1 and 100.

    Sub ValidationNumbers()
    ' Select cell A1
    With Range("A1").Validation
        ' Clear any existing validation
        .Delete
        ' Apply whole number validation
        .Add Type:=xlValidateWholeNumber, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="1", Formula2:="100"
        ' Custom error message
        .ErrorMessage = "Please enter a number between 1 and 100."
        .ShowError = True
    End With
End Sub

    Explanation:

    • Range(« A1 »).Validation applies validation to cell A1.
    • Type:=xlValidateWholeNumber specifies that only whole numbers are allowed.
    • Formula1:= »1″, Formula2:= »100″ sets the range for valid numbers between 1 and 100.
    • .ErrorMessage sets a custom error message.
    • .ShowError = True shows the error message if the entered value does not meet the validation.
    1. Validation for Allowing Only Dates

    This example applies data validation to a range of cells (e.g., B1:B10) to allow only dates within a specific range.

    Sub ValidationDates()
    ' Apply validation to range B1:B10
    With Range("B1:B10").Validation
        ' Clear any existing validation
        .Delete
        ' Apply date validation
        .Add Type:=xlValidateDate, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="01/01/2020", Formula2:="31/12/2025"
        ' Custom error message
        .ErrorMessage = "Please enter a date between 01/01/2020 and 31/12/2025."
        .ShowError = True
    End With
End Sub

    Explanation:

    • xlValidateDate specifies that only dates are allowed.
    • Formula1:= »01/01/2020″, Formula2:= »31/12/2025″ sets the valid date range.
    • If the user enters a non-date value or a date outside the specified range, the custom error message will appear.
    1. Creating a Dropdown List

    This example creates a dropdown list in cell C1 with predefined options: « Option 1 », « Option 2 », « Option 3 ».

    Sub DropdownList()
    ' Apply dropdown list validation to cell C1
    With Range("C1").Validation
        ' Clear any existing validation
        .Delete
        ' Create a dropdown list with fixed values
        .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="Option 1,Option 2,Option 3"
        ' Custom error message
        .ErrorMessage = "Please select an option: Option 1, Option 2, Option 3."
        .ShowError = True
    End With
End Sub

    Explanation:

    • xlValidateList specifies that a dropdown list is to be applied.
    • Formula1:= »Option 1,Option 2,Option 3″ defines the options in the list.
    • If the user enters anything other than the available options, the error message will be shown.
    1. Custom Validation with a Formula (e.g., Text Starting with a Specific Letter)

    This example validates that the text entered in cell D1 starts with the letter « A ».

    Sub TextValidation()
    ' Apply validation to cell D1
    With Range("D1").Validation
        ' Clear any existing validation
        .Delete
        ' Apply custom validation with a formula
        .Add Type:=xlValidateCustom, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="=Left(D1,1)=""A"""
        ' Custom error message
        .ErrorMessage = "Text must start with the letter 'A'."
        .ShowError = True
    End With
End Sub

    Explanation:

    • xlValidateCustom allows us to use a formula for custom validation.
    • Formula1:= »=Left(D1,1)= » »A » » » checks if the first character in D1 is « A » using the LEFT function.
    • If the condition is not met, the custom error message will be displayed.

    Complete VBA Code for All Validations

    Here’s a complete VBA code that applies all the above validations to a worksheet:

    Sub ApplyValidations()
    ' Apply number validation in cell A1
    ValidationNumbers   
    ' Apply date validation in range B1:B10
    ValidationDates   
    ' Apply dropdown list in cell C1
    DropdownList 
    ' Apply text validation in cell D1
    TextValidation
End Sub

    How to Run This Code:

    1. Open Excel and press Alt + F11 to open the VBA editor.
    2. In the left pane, right-click on « VBAProject (YourWorkbookName) », then select Insert > Module.
    3. Paste the code into the new module.
    4. Close the VBA editor.
    5. You can now run the macro by pressing Alt + F8, then selecting ApplyValidations.

    Conclusion

    This code demonstrates how to automate various data validation rules in Excel using VBA. You can customize these validations according to your specific needs, such as setting different ranges, validation types, or custom criteria.

  • Automate data synchronization processes in Excel using VBA

    Automating data synchronization processes in Excel using VBA (Visual Basic for Applications) is a common task when you need to update or transfer data between multiple sheets or data sources. Below is a detailed example of VBA code to automate this synchronization, along with explanations for each step of the process.

    Scenario

    Let’s assume you have a « Source » sheet with data and a « Destination » sheet where you want to copy or synchronize this data. The goal is to copy data from the « Source » sheet to the « Destination » sheet, but only the new or updated data.

    1. Sheet Structure
    • Source: Contains the current data.
    • Destination: Contains the old data and needs to be updated.
    1. VBA Code for Data Synchronization
    Sub SynchronizeData()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim lastRowSource As Long
    Dim lastRowDestination As Long
    Dim i As Long
    Dim found As Boolean
    Dim cellSource As Range
    Dim cellDest As Range
    ' Reference to the Source and Destination sheets
    Set wsSource = ThisWorkbook.Sheets("Source")
    Set wsDestination = ThisWorkbook.Sheets("Destination")
    ' Determine the last used row in both sheets
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    lastRowDestination = wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Row
    ' Loop through the data in the Source sheet
    For i = 2 To lastRowSource ' Assuming the first row contains headers
        Set cellSource = wsSource.Cells(i, 1) ' Column A, adjust if necessary
        ' Search if the data already exists in the Destination sheet
        found = False
        For Each cellDest In wsDestination.Range("A2:A" & lastRowDestination)
            If cellSource.Value = cellDest.Value Then
                found = True
                Exit For
            End If
        Next cellDest
        ' If the data is not found, copy it to the Destination sheet
        If Not found Then
            wsDestination.Cells(lastRowDestination + 1, 1).Value = cellSource.Value
            wsDestination.Cells(lastRowDestination + 1, 2).Value = wsSource.Cells(i, 2).Value ' Copy column B (adjust as needed)
            lastRowDestination = lastRowDestination + 1 ' Update the last used row in Destination
        End If
    Next i
    ' Message when synchronization is complete
    MsgBox "Data synchronization is complete!", vbInformation
End Sub
    1. Code Explanation

    a) Variable Declarations

    • wsSource and wsDestination: References to the Source and Destination worksheets, respectively.
    • lastRowSource and lastRowDestination: Variables used to determine the last row with data in column A of both sheets.
    • i, found, cellSource, cellDest: Variables for looping and checking the data to be copied.

    b) Finding the Last Used Rows

    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
lastRowDestination = wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Row

    This code determines where the last row of data is in both the Source and Destination sheets, ensuring that we don’t exceed the usable range.

    c) Loop Through the Source Sheet

    The loop For i = 2 To lastRowSource goes through each row in the Source sheet (assuming the first row contains headers). For each iteration, it checks the cell in column A (you can adjust this if needed).

    d) Searching for Data in the Destination Sheet

    For each piece of data from the Source sheet, the code checks if that value already exists in the Destination sheet within the range « A2:A ». If a match is found, it skips to the next data entry.

    e) Copying Data to the Destination Sheet

    If the data is not found in the Destination sheet, it is copied to the next available row in the Destination sheet (column A in this case, but you can add more columns if needed).

    wsDestination.Cells(lastRowDestination + 1, 1).Value = cellSource.Value

    This line copies the value from the Source cell to the next empty row in column A of the Destination sheet.

    f) Updating the Last Used Row in the Destination

    After each copy, the lastRowDestination variable is updated to reflect the last used row in the Destination sheet.

    g) Completion Message

    After the synchronization is complete, a message box appears to notify the user that the synchronization is done.

    1. Customization
    • Multiple Columns: If you want to synchronize more than one column, you can customize the code to copy additional columns. For example, if you want to copy columns A and B from the Source sheet to columns A and B in the Destination sheet, you can add lines like this:
    wsDestination.Cells(lastRowDestination + 1, 1).Value = wsSource.Cells(i, 1).Value
wsDestination.Cells(lastRowDestination + 1, 2).Value = wsSource.Cells(i, 2).Value
    • Dynamic Ranges: If the data isn’t in a specific column or you have a varying number of columns, you can adjust the range and indices accordingly.
    1. How to Run This Code

    Open Excel and press Alt + F11 to open the VBA editor.

    In the VBA editor, click on Insert then Module to create a new module.

    Paste the code into this module.

    Close the VBA editor and return to your Excel workbook.

    Press Alt + F8, select SynchronizeData, and click Run.

    Conclusion

    This code automates the synchronization of data between two Excel sheets, copying only the new data from the Source sheet to the Destination sheet. You can adapt it for more complex cases, such as handling multiple columns or more advanced data comparison.

     

  • Automate data summarization tasks in Excel using VBA

    Automating data summarization tasks in Excel using VBA (Visual Basic for Applications) is an excellent way to save time and increase efficiency. The goal is to centralize data, summarize or analyze it based on specific criteria, and generate reports or charts automatically.

    Example: Automating Data Summarization with VBA

    Let’s assume you have a sales data sheet, and you want to create a summary that calculates total sales by product, region, and month.

    Here’s a detailed VBA code example for automating this process:

    1. Data Preparation

    Assume you have a data sheet structured as follows (named « Data »):

    Date Product Region Sales
    01/01/2024 A North 100
    02/01/2024 B South 150
    03/01/2024 A East 200
    … … … …
    1. Objective

    The objective is to create an automatic summary that:

    • Calculates total sales by product.
    • Calculates total sales by region.
    • Calculates total sales by month.

    We will write a VBA code to generate this summary in a new sheet.

    1. VBA Code to Automate Data Summarization

    Here’s the detailed VBA code:

    Sub SalesSummary()
    ' Variable declarations
    Dim wsSource As Worksheet
    Dim wsSummary As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim dictProducts As Object
    Dim dictRegions As Object
    Dim dictMonths As Object
    Dim monthDate As String
    Dim product As String
    Dim region As String
    Dim sales As Double   
    ' Initialize dictionaries for calculations
    Set dictProducts = CreateObject("Scripting.Dictionary")
    Set dictRegions = CreateObject("Scripting.Dictionary")
    Set dictMonths = CreateObject("Scripting.Dictionary")   
    ' References to the sheets
    Set wsSource = ThisWorkbook.Sheets("Data")
    On Error Resume Next
    Set wsSummary = ThisWorkbook.Sheets("Summary")
    On Error GoTo 0   
    ' If the "Summary" sheet exists, delete it
    If Not wsSummary Is Nothing Then
        Application.DisplayAlerts = False
        wsSummary.Delete
        Application.DisplayAlerts = True
    End If   
    ' Create a new "Summary" sheet
    Set wsSummary = ThisWorkbook.Sheets.Add
    wsSummary.Name = "Summary"   
    ' Find the last row of data
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row   
    ' Loop through the data and fill the dictionaries
    For i = 2 To lastRow ' Start at row 2 to ignore the header
        monthDate = Format(wsSource.Cells(i, 1).Value, "yyyy-mm") ' Extract year and month
        product = wsSource.Cells(i, 2).Value
        region = wsSource.Cells(i, 3).Value
        sales = wsSource.Cells(i, 4).Value       
        ' Update the product dictionary
        If Not dictProducts.Exists(product) Then
            dictProducts.Add product, 0
        End If
        dictProducts(product) = dictProducts(product) + sales       
        ' Update the region dictionary
        If Not dictRegions.Exists(region) Then
            dictRegions.Add region, 0
        End If
        dictRegions(region) = dictRegions(region) + sales       
        ' Update the month dictionary
        If Not dictMonths.Exists(monthDate) Then
            dictMonths.Add monthDate, 0
        End If
        dictMonths(monthDate) = dictMonths(monthDate) + sales
    Next i   
    ' Add headers to the "Summary" sheet
    wsSummary.Cells(1, 1).Value = "Criteria"
    wsSummary.Cells(1, 2).Value = "Total Sales"   
    ' Fill in the product results
    wsSummary.Cells(2, 1).Value = "By Product"
    wsSummary.Cells(3, 1).Value = "Product"
    wsSummary.Cells(3, 2).Value = "Total Sales"
    i = 4
    For Each Key In dictProducts.Keys
        wsSummary.Cells(i, 1).Value = Key
        wsSummary.Cells(i, 2).Value = dictProducts(Key)
        i = i + 1
    Next Key   
    ' Add a blank row between sections
    i = i + 1   
    ' Fill in the region results
    wsSummary.Cells(i, 1).Value = "By Region"
    wsSummary.Cells(i + 1, 1).Value = "Region"
    wsSummary.Cells(i + 1, 2).Value = "Total Sales"
    i = i + 2
    For Each Key In dictRegions.Keys
        wsSummary.Cells(i, 1).Value = Key
        wsSummary.Cells(i, 2).Value = dictRegions(Key)
        i = i + 1
    Next Key   
    ' Add a blank row between sections
    i = i + 1   
    ' Fill in the month results
    wsSummary.Cells(i, 1).Value = "By Month"
    wsSummary.Cells(i + 1, 1).Value = "Month"
    wsSummary.Cells(i + 1, 2).Value = "Total Sales"
    i = i + 2
    For Each Key In dictMonths.Keys
        wsSummary.Cells(i, 1).Value = Key
        wsSummary.Cells(i, 2).Value = dictMonths(Key)
        i = i + 1
    Next Key   
    ' Formatting
    wsSummary.Columns("A:B").AutoFit
    wsSummary.Cells(1, 1).Font.Bold = True
    wsSummary.Cells(1, 2).Font.Bold = True  
    MsgBox "Summary complete!", vbInformation
End Sub

    Code Explanation

    1. Variable Declarations
      • wsSource: Reference to the worksheet containing the raw data (« Data »).
      • wsSummary: Reference to the worksheet where the summary will be created.
      • dictProducts, dictRegions, dictMonths: Dictionaries used to store and accumulate sales by product, region, and month.
      • lastRow: To find the last row of data in the source sheet.
    2. Initializing Dictionaries
      • We use dictionaries to store accumulated sales data. This makes it easy to add and update items based on the keys (product, region, and month).
    3. Loop Through Data
      • The loop For i = 2 To lastRow processes each row in the « Data » sheet, starting from the second row to skip the header.
      • For each row, we extract the date, product, region, and sales, and update the corresponding dictionaries.
    4. Creating the Summary
      • After accumulating the data in the dictionaries, we generate a new sheet called « Summary » and insert the totals by product, region, and month.
    5. Formatting
      • The AutoFit method is used to automatically adjust column widths.
      • The headers are formatted in bold.

    How to Use the Code

    To use this VBA code:

    1. Open your Excel file.
    2. Press Alt + F11 to open the VBA editor.
    3. Go to Insert > Module and paste the code.
    4. Press F5 or run the macro from the Excel interface under the Developer > Macros menu.

    This will generate a complete summary of sales by product, region, and month on a new sheet. You can adjust this code according to your specific needs.

  • Automate data synthesis in Excel with VBA

    Automating data synthesis in Excel with VBA can be very useful for saving time and reducing human errors. Below is a detailed example of VBA code that can be used to automate this process. The goal of this code is to consolidate data from multiple sheets into a single summary table.

    Scenario

    Imagine that we have several worksheets in an Excel workbook, each containing similar data (for example, sales data from different regions), and we want to automate the process of consolidating this data into a « Summary » sheet.

    Step 1: Preparing the Data

    Assume that each sheet contains sales data with the following columns:

    • Product Name
    • Quantity Sold
    • Revenue

    We want to compile this information into a summary sheet.

    Step 2: Structure of the Code

    Here’s an example of VBA code that loops through each worksheet, retrieves the data, and copies it to a summary sheet.

    VBA Code for Automating Data Synthesis

    1. Access the VBA editor:
      • Press Alt + F11 to open the VBA editor.
      • Click on Insert and then Module to insert a new module.
    2. Write the VBA code:
    Sub AutomateSynthesis()
    ' Declare variables
    Dim wsSummary As Worksheet
    Dim ws As Worksheet
    Dim i As Long, lastRow As Long, summaryRow As Long
    Dim dataRange As Range
    Dim cell As Range   
    ' Create or activate the Summary sheet
    On Error Resume Next
    Set wsSummary = ThisWorkbook.Sheets("Summary")
    On Error GoTo 0   
    If wsSummary Is Nothing Then
        ' If the "Summary" sheet does not exist, create it
        Set wsSummary = ThisWorkbook.Sheets.Add
        wsSummary.Name = "Summary"
    End If   
    ' Clear old data in the Summary sheet
    wsSummary.Cells.Clear
    ' Add headers for the summary
    wsSummary.Cells(1, 1).Value = "Product Name"
    wsSummary.Cells(1, 2).Value = "Total Quantity"
    wsSummary.Cells(1, 3).Value = "Total Revenue"   
    summaryRow = 2  ' Row to start pasting data   
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Skip the Summary sheet itself
        If ws.Name <> "Summary" Then
            ' Find the last row of data in the current sheet
            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            ' Set the data range to copy (from A2 to the last row in columns A-C)
            Set dataRange = ws.Range("A2:C" & lastRow)           
            ' Copy data to the Summary sheet
            For Each cell In dataRange.Rows
                ' Copy each row of data to the Summary sheet
                wsSummary.Cells(summaryRow, 1).Value = cell.Cells(1, 1).Value  ' Product Name
                wsSummary.Cells(summaryRow, 2).Value = wsSummary.Cells(summaryRow, 2).Value + cell.Cells(1, 2).Value ' Total Quantity
                wsSummary.Cells(summaryRow, 3).Value = wsSummary.Cells(summaryRow, 3).Value + cell.Cells(1, 3).Value ' Total Revenue      
                ' Move to the next row in the Summary sheet
                summaryRow = summaryRow + 1
            Next cell
        End If
    Next ws
    ' Message box to indicate the synthesis is complete
    MsgBox "Data synthesis has been completed successfully!", vbInformation
End Sub

    Code Explanation

    1. Variable declarations:
      • wsSummary: This is the variable for the « Summary » sheet.
      • ws: This represents each worksheet.
      • i, lastRow, summaryRow: These variables manage the row positions.
      • dataRange: This holds the range of data to copy.
      • cell: This variable is used to loop through each row of data.
    2. Create or activate the summary sheet:
      • The code first checks if a sheet named « Summary » already exists. If it doesn’t, it creates one.
    3. Clear old data:
      • If the summary sheet already exists, the previous data is cleared using Cells.Clear.
    4. Add headers:
      • Headers like « Product Name, » « Total Quantity, » and « Total Revenue » are added in row 1 of the summary sheet.
    5. Loop through each sheet:
      • The code loops through each worksheet in the workbook, skipping the « Summary » sheet.
      • For each sheet, it finds the last row of data (lastRow) and defines the data range to copy (dataRange).
    6. Copy the data:
      • For each row in the data range, the code copies the product name, quantity, and revenue into the summary sheet.
      • It accumulates the totals in the « Total Quantity » and « Total Revenue » columns.
    7. Confirmation message:
      • Once the automation is complete, a message box is displayed to inform the user that the synthesis has been completed successfully.

    Step 3: Running the Code

    To execute the code:

    1. In the VBA editor, press F5 or go to « Run » > « Run Sub/UserForm. »
    2. The code will loop through all sheets, collect the data, and paste it into the « Summary » sheet.

    Step 4: Customization

    • You can adapt this code to include additional types of summaries, such as averages, specific filters, or even generate charts.
    • The code can be enhanced to handle specific errors, such as empty cells or incorrect formats.

    This code provides a simple framework for automating data consolidation in Excel, and you can modify it to suit your specific needs!