What I'm looking to do, is automatically transfer rows of data between Excel workbooks. I'd like to be able to have a row of data in one workbook, with a drop down list at the end which has a number of options - for example "proceed to next stage" or "don't proceed". If you were to select "proceed to next stage" from the drop down list, I'd like the data to automatically populate on the next available row in a second workbook. Ideally it would only be certain cells of data from the row in the first workbook populated into the second, but to be honest that's not a total necessity. If possible, I'd like to reverse the auto population process as well if necessary. So if you change the drop down option at the end of the row to "don't proceed", it would then remove the data previously copied over to the second document.
Any ideas? Cheers.
Nice of you to start off with something easy...
The first thing I always think of is, could I do this manually?
If I can do it manually, then I can record it into a macro.
If I can record it into a macro, then I can call it from somewhere (and amend the macro code).
The hardest bit here might be 'the next available row' thing.
In absolute honesty, I'll need to have a play with it for a bit and do some googling.
Interesting challenge this, happy to have a dabble, but in all honesty beyond me how to do it off the top of my head.
Wouldn't be at all surprised if there's someone here more current with the tool who can offer a better solution...
(Note remove the three backticks ‘’’vba before and the three backtick ‘’’ after the code)Creating a VBA (Visual Basic for Applications) script to transfer data between workbooks based on a specific condition can be quite involved but highly customizable. Below is a simplified version of how you could set this up, making some assumptions about your workbook structure and requirements.
1. Ensure that your data in `Source.xlsx` is organized in a table format.
2. Assume that the table in `Source.xlsx` has a column named "Status" which will have the value "Proceed to next stage" or "Don't Proceed", based on your selection from a dropdown list.
**Steps to Create the VBA Script**:
1. **Open the Source Workbook (`Source.xlsx`)**:
- Open `Source.xlsx`.
- Press `Alt + F11` to open the VBA editor.
2. **Insert a New Module**:
- In the VBA editor, right-click on `VBAProject (Source.xlsx)` in the left-hand pane.
- Select `Insert` -> `Module`.
3. **Write the VBA Script**:
- In the module window, paste the following script:
Dim sourceSheet As Worksheet
Dim destWb As Workbook
Dim destSheet As Worksheet
Dim lastRowSource As Long
Dim lastRowDest As Long
Dim i As Long
' Set references to source sheet, destination workbook and destination sheet
Set sourceSheet = ThisWorkbook.Sheets("Sheet1") ' Adjust "Sheet1" to your source sheet name
Set destWb = Workbooks.Open("C:\path\to\Destination.xlsx") ' Adjust path to your destination workbook
Set destSheet = destWb.Sheets("Sheet1") ' Adjust "Sheet1" to your destination sheet name
' Clear previous data in destination sheet
' Find the last row of data in the source sheet
lastRowSource = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
' Loop through each row in the source sheet
For i = 2 To lastRowSource ' Assuming headers are in row 1
If sourceSheet.Cells(i, "Status").Value = "Proceed to next stage" Then ' Adjust "Status" to your status column name
' Find the next available row in the destination sheet
lastRowDest = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row + 1
' Copy the entire row from source to destination
' Save and close the destination workbook
' Notify the user
MsgBox "Data transfer complete."
4. **Adjust the Script**:
- Change `"Sheet1"` to the name of your source and destination sheets.
- Change `"C:\path\to\Destination.xlsx"` to the path of your `Destination.xlsx` file.
- Change `"Status"` to the name of your status column if it's different.
5. **Run the Script**:
- Press `F5` to run the script and transfer the data.
6. **Add an Event Trigger (Optional)**:
- If you want to automate this process further, you could set up an event trigger to run this script whenever a certain condition is met, like saving the workbook or changing a cell value.
7. **Create a Button (Optional)**:
- You could also create a button in your source workbook that runs this script when clicked, providing a user-friendly way to transfer the data.
This script checks each row in the source sheet, and if the status is "Proceed to next stage", it copies the entire row to the next available row in the destination sheet. Before doing this, it clears the previous data in the destination sheet to ensure that rows are removed if they no longer meet the criteria.
Remember to save and backup your workbooks before running any VBA script to prevent data loss in case of errors.
Got to say, if you’re doing stuff across multiple workbooks at this point you’ve probably outgrown Excel and should look at a proper data management system like a database or a SharePoint system.
Next best would be Google Sheets as it’s far more programmable and linkable.
If you insist on building apps in Excel like an absolute mad man there’s a few hacky ways you could do this.
Use Power Query to import the data (Data > Get and Transform Data) and only pull in the rows you need from the source file. You’d need to clear out the sheet and refresh the query each time to ensure rows that change back are removed though.
Write a VB script that triggers on a button or data change. Here’s ChatGPT to get you started:
(Note remove the three backticks ‘’’vba before and the three backtick ‘’’ after the code)
Or you could use something like Zapier or MS Power Automate to trigger an automation on data change. But that’s probably the least reliable and requires your data to be accessible online.
+1 for @shmmeee advice here.
We'd perhaps differ on whether Excel is appropriate for this - it can be and I've seen it used this way in various places I've worked. It's ok if you're a "power user" with a reasonable amount of experience, but tough to do and get right if you're not.
I've spent a moderate amount of my working life converting this kind of Excel stuff into more formal database structures, and some of the spreadsheets I've worked on clearly weren't doing the thing that they were supposed to!
That said, in the absence of better tools, I think Excel will work for this. I've no experience of Google sheets, but as shmmeee says that might be an even better option...
Thanks for all the suggestions. In normal circumstances I would be open to trying other software for what I want to achieve, but in this case it has to be compatible for work within Microsoft Teams.
I totally hear you. Just for your future data engineers sake, please document your files somewhere at least.
Do you mean like save elsewhere? Because I do a weekly download of the live file and replace the version I have saved on the servers. Just so there's always an offline copy available which is almost up to date.
I mean whatever crazy business logic you’ve got hidden inside there, make a note of it for the inevitable next person who has to work out what you’re thinking. As you may have guessed my job is often to be that person.
It was somewhere around here that all the shit srarted re the site.Do you mean like save elsewhere? Because I do a weekly download of the live file and replace the version I have saved on the servers. Just so there's always an offline copy available which is almost up to date.