Forum Discussion

elianaelia's avatar
elianaelia
Brass Contributor
May 17, 2024

Automatically update data in another workbook

Please for your help!

I have two worksheets, one is the source and the second one is the destination. In the source workbook I have a list of project names. The project names are listed in column A . Adjacent to column A, are in columns the months, where the employees will manually add the percentage of hours they worked for each project for that month.


I managed with power query to link the data in the destination file so when I add a new project in the source workbook automatically is added in the correct row of the column A in the destination workbook. However, when a new project is added in the destination workbook, creates only a cell and not the whole row. I do not want to link the whole table (this would have added a new row), I want the new row to be empty and ready to be filled by a user.

How can I automatically create a new row with filled the column A and empty the rest columns of the row when a new project is added in the source workbook?

Many thanks in advance!

  • To automatically update a destination workbook with a new row for each new project added in the source workbook, you can use VBA (Visual Basic for Applications) to handle this task. Since Power Query does not directly provide functionality to add empty rows when new data is inserted, VBA can be used to fill this gap by automatically creating the necessary rows and ensuring they are empty, except for the project name.

    Here's a step-by-step approach to achieve this:

    Step 1: Set Up the Workbooks
    Source Workbook: Contains project names in Column A and percentage data for each month in subsequent columns.
    Destination Workbook: Should have the same structure as the source workbook but with empty rows ready to be filled.
    Step 2: VBA Code to Update the Destination Workbook
    You’ll need to add VBA code to the destination workbook that will:

    Check for new projects in the source workbook.
    Add corresponding rows in the destination workbook with the project names and empty cells for other columns.
    Here's a sample VBA code to get you started:

    VBA Code to Insert New Rows

    vba
    Copy code
    Sub UpdateDestinationWorkbook()
    Dim sourceWorkbook As Workbook
    Dim destinationWorkbook As Workbook
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim sourceCell As Range
    Dim lastRowSource As Long
    Dim lastRowDestination As Long
    Dim projectName As String
    Dim foundCell As Range

    ' Set the workbooks and sheets
    Set sourceWorkbook = Workbooks("SourceWorkbook.xlsx") ' Replace with the name of your source workbook
    Set destinationWorkbook = Workbooks("DestinationWorkbook.xlsx") ' Replace with the name of your destination workbook
    Set sourceSheet = sourceWorkbook.Sheets("Sheet1") ' Replace with the name of the sheet in source workbook
    Set destinationSheet = destinationWorkbook.Sheets("Sheet1") ' Replace with the name of the sheet in destination workbook

    ' Find the last rows
    lastRowSource = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row
    lastRowDestination = destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp).Row

    ' Loop through the source sheet and update the destination sheet
    For Each sourceCell In sourceSheet.Range("A1:A" & lastRowSource)
    projectName = sourceCell.Value
    If projectName <> "" Then
    ' Check if the project already exists in the destination sheet
    Set foundCell = destinationSheet.Range("A:A").Find(What:=projectName, LookIn:=xlValues, LookAt:=xlWhole)

    ' If not found, add it to the destination sheet
    If foundCell Is Nothing Then
    destinationSheet.Cells(lastRowDestination + 1, 1).Value = projectName
    lastRowDestination = lastRowDestination + 1
    End If
    End If
    Next sourceCell
    End Sub
    Step 3: Running the VBA Code
    Open Excel and press ALT + F11 to open the VBA editor.
    Insert a New Module:
    Go to Insert > Module to add a new module.
    Paste the Code:
    Copy and paste the above VBA code into the module.
    Adjust the Workbook and Worksheet Names:
    Replace "SourceWorkbook.xlsx", "DestinationWorkbook.xlsx", and the sheet names with your actual workbook and sheet names.
    Run the Code:
    Press F5 to run the code or close the VBA editor and run the macro from Excel by pressing ALT + F8, selecting UpdateDestinationWorkbook, and clicking Run.
    Step 4: Automate the Process
    To automate this process, you can set up a scheduled task or run the macro whenever you open or update the destination workbook:

    Run on Workbook Open:
    Add the following code to the ThisWorkbook object in the VBA editor for the destination workbook:
    vba
    Copy code
    Private Sub Workbook_Open()
    Call UpdateDestinationWorkbook
    End Sub

Resources