1

Please verify if this is possible...

I have a team of 5. We each have local excel files we use as project trackers. We want to collaborate on a high level using a master project tracker in Excel Online on OneDrive but individually continue to use our local trackers to update more detailed information on each project.

I understand Excel Online cannot run macros. But is it possible for our local files to each run a macro that copy and pastes our weekly project updates automatically to this master tracker in Excel Online?

I have not found anything on this. I am wondering if this is even possible to do or if anyone has a better solution than this for what we need.

Below is code to copy and paste from one workbook to another. How can this be altered to copy and paste to an Excel Online Workbook? If this is even possible.

Sub Copy_Paste_Below_Last_Cell()
'Find the last used row in both sheets and copy and paste data below existing data.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

  'Set variables for copy and destination sheets
  Set wsCopy = Workbooks("New Data.xlsx").Worksheets("Export 2")
  Set wsDest = Excel Online Workbook

  '1. Find last used row in the copy range based on data in column A
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

  '2. Find first blank row in the destination range based on data in column A
  'Offset property moves down 1 row
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

  '3. Copy & Paste Data
  wsCopy.Range("A2:D" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Steven Gilbert
  • 35
  • 1
  • 2
  • 9
  • An „Excel Online“ also is a file. Its OneDrive-Location can be synchronized with each of your local computers and you can each treat the file like a local file. If you work manually together within it, you can see each others cell cursor. However a VBA treatment may lead to trouble - I don‘t have experience with that. – Asger Apr 10 '19 at 17:51
  • 1
    You can create an Excel Online workbook and store all the data centrally on one or more worksheets. Then, each local Excel file would essentially perform as a "client" of the central database, you would have to manage when to save data from the client to the central workbook -- this would potentially involve data synchronization, data lockouts while a client accesses the data, and a whole host of other well understood sync issues. MS Access has much of this capability built-in if you can use MS Access Online or other similar databases. – PeterT Apr 10 '19 at 20:40
  • Thank you. These are options I will be looking into. I will make the Excel Online Version Read-only to prevent syncing/lock-out issues and still give visibility to each team member of what each other are working on. It will be traced to a master spreadsheet contained on our shared drive. Each member of the team will have a local file that they use to track only their personal projects and their spreadsheet will contain a macro to update the master spreadsheet on the shared drive. – Steven Gilbert Apr 11 '19 at 15:43

0 Answers0