VBA to save a sheet in shared Onedrive folder

[Kine]

Hi Everyone.

I have an Excel file with many sheets stored in my Onedrive. I would like to select a specific sheet of that Exel file and save it as a new Excel file in a shared folder of my Onedrive. I would like other users (with different Onedrive accounts) to access the file from the shared folder and to be able to use it. Also, I would like to do this often so the old shared file need to be replaced by the new one (containing extra data) instead of the code creating a new file.
I think this has 4 main challenges:
- Make the file accessible to users with different Onedrives from any device (no local path I think)
- Enable me to replace the existing file without the vba code asking " are you sure"
- Initially close any file that would contain the same name as my shared file so that any "saveas" function would work with no issue


Could someone kindly help me with the code that would do this? I am not a VBA expert but I can adapt an existing code.

Many thanks

 

[rlv01]

Use the Excel Macro Recorder to record yourself performing the action of selecting a worksheet from you master file, copying it to a new workbook, then saving the new workbook to your OneDrive folder you have described above. Then post the code generated by the macro recorder here and we will see if it is possible to automate it to make it more broadly applicable.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[Kine]

Thank you very much. Actually I never knew this recorder existed. I will look to see how it works. Thanks