Update Spreadsheet From Remote Spreadsheet
Posted on 2014-01-26
I'd like to create a button that will update a worksheet in my workbook with a worksheet from a remote workbook (different location).
I'd like to have this happen:
- User opens workbook
- Clicks button that says "Update Master"
- The "Master" worksheet columns B through I are deleted and replaced with columns A through H from the remote worksheet
Some things to note:
I currently update this manually and have to put the sheet in design mode (click on Developer Tab, then Design Mode Tab) because I have some vb code that sorts the Master sheet when a value is changed on another sheet in the workbook.
Once I replace columns B through I, I click on the Design Mode Tab again to take sheet out of Design Mode to make it useable again before sending to users.
I have a formula in column A which concatenates columns B and C, which is why I only update B through I:
=IF(OR(B2="A Continuation of Previous Course",B2="A New Course"),B2,C2&" "&" "&B2)
Spreadsheet is protected (no password).
"Master" worksheet is hidden.
I can schedule the remote spreadsheet to update daily with the data I need in these columns (B through I). This data is exported from our database into an excel spreadsheet but it starts at Column A, thus making the new data in Columns A through H.
I'd like to automate this by allowing the user to click a button and it updates so they'll have the most current information in the spreadsheet - thus eliminating my manual process.
Also would like to notify user when process is complete so they know to begin using the sheet.