How to Copy a Worksheet from a SharePoint Macro-Enabled Workbook Using VBA
Question details
The user needs a reliable way to run a VBA macro that copies, replaces, and updates a worksheet within a macro-enabled workbook hosted on SharePoint.
- Product
- Microsoft Excel / SharePoint
- Device & OS
- not provided
- Scenario
- Attempting to use a VBA script to copy a worksheet from a SharePoint-hosted macro-enabled workbook (.xlsm) to another workbook, rename it, and adjust page setup settings.
- Observed behavior
- The VBA script executes perfectly when files are stored locally, but encounters errors, limitations, or fails to perform workbook and worksheet operations when the file is opened directly via a SharePoint URL.
Ensure you have the OneDrive desktop application installed and signed in with the Microsoft account authorized to access your organization's SharePoint library.
Synchronize the SharePoint Document Library to OneDrive
By syncing the SharePoint folder to your local PC via OneDrive, you provide VBA with a local file path. This bypasses the limitations of running VBA directly against HTTPS SharePoint URLs.
VBA was originally designed for local file systems and network drives. When standard VBA commands like 'Workbooks.Open' or 'Sheets.Copy' interact directly with web-based SharePoint URLs, they frequently time out or throw errors. Syncing the files locally ensures complete compatibility with legacy VBA operations.
Navigate to the exact SharePoint document library where your macro-enabled workbook (.xlsm) is stored.
Click the 'Sync' button located in the top toolbar of the SharePoint document library. Confirm any prompts to open Microsoft OneDrive.
Open File Explorer on your computer. Look for a folder in the left navigation pane named after your organization (e.g., building icon). Inside, you will find the synced SharePoint folder.
Open the macro-enabled workbook from this new local directory. Because the file now has a local path (like C:\Users\Name\Org\Folder\File.xlsm), your VBA code for copying worksheets and saving changes will execute seamlessly.
Experience Seamless Macro Execution with WPS Office
If SharePoint and Excel VBA restrictions are slowing down your productivity, consider WPS Office. It is a highly compatible, lightweight Office alternative that offers excellent support for local macro execution and VBA programming without the heavy overhead.
- 1. Download and Install: Visit the official WPS Office website to download and install the free software suite.
- 2. Open your Workbook: Launch WPS Spreadsheet and open your existing .xlsm macro-enabled workbook directly.
- 3. Run Macros Seamlessly: Navigate to the Developer tab to access the Macro editor, run your scripts, and automate your worksheet copying processes locally.
Frequently Asked Questions
Why does my VBA script fail when using a SharePoint URL?
Traditional VBA methods use standard file paths. SharePoint uses HTTP/HTTPS protocols, which VBA functions like 'Dir()', 'SaveAs', and 'Sheets.Copy' struggle to parse or gain write access to reliably without specialized web request coding.
Can I use Power Automate instead of VBA for SharePoint files?
Yes. Microsoft Power Automate (formerly Microsoft Flow) is natively integrated with SharePoint and is generally recommended over VBA for cloud-based file manipulation, data extraction, and moving files between cloud locations.
How do I dynamically reference the synced OneDrive folder in VBA?
Instead of hardcoding the 'C:\Users\JohnDoe\' path, you can use 'Environ("USERPROFILE") & "\Company Name\SharePoint Folder\"' in your VBA script to ensure the macro works for any user who has synced the folder.
Do macros created in Excel work in WPS Office?
Yes, WPS Office Pro and certain other versions feature extensive VBA compatibility, meaning most standard macros written in Excel (.xlsm) can run natively within WPS Spreadsheet without modification.
