Forum Discussion
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!
- AshaKantaSharmaIron ContributorTo 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