I have excel two files used by two separate people. File A (source file) has three worksheets, with Sheet #1's raw data needing to be copied to File B (destination file)

• Both File A and File B are saved over Onedrive and edited via web-access
• Only Sheet #1's data needs to be copied. Sheet #2 and #3 are irrelevant
• The data needs to be inputted into a table on File B
• All formats and table structures are default / standard. I haven't made any changes.
• Both files will be continuously updated

I tried Power Automate to push the data between the files, which worked with a minute delay. However I've been asked to try a different method as it's failed for a different task in the past. It's a shame that my Automate flow keeps getting interrupted, I'd fix it if I knew what the dam problem was.

I can't use the Get-Data tool in Excel as my files need to be updated over OneDrive. I have to locally open File A in order for it to update to File B, but we use web-access frequently so that's a no-go.

I've read that VBA Macros could fix my problem. However they look challenging to setup so I would appreciate some insight first. I can't spend a day on something that won't work.

I have little experience when it comes to Excel so any advice appreciated! Also please let me know if there's a simpler fix for my problem