Automatically update data in another workbook

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