none
Chart Does not Copy when Workbook is Shared RRS feed

  • Question

  • Excel 2013.

    I have a VBA procedure that cycles through workbooks and copies a range of data from the workbooks into the master workbook (in which the VBA module resides).  The range of data includes a table and a chart.  The code works fine when the workbook is not shared, but does not copy the chart when the workbook is shared (with "Allow multiple users to edit" checked).  I am perplexed why this is happening.

    The code that does the copy is below, with the part that copies the range that includes the table and chart in bold.

          strRange = "A1:T20"
          strRange2 = "A" + Trim(Str(I)) + ":T" + Trim(Str(I + 20))
          'Copy the Dashboard table and chart into Master Dashboard in this book.
          wb.Worksheets("Dashboard").Range(strRange).Copy ThisWorkbook.Sheets("Master Dashboard").Range(strRange2)
    


    Doug Pruiett

    Wednesday, October 19, 2016 7:46 PM

All replies

  • From testing, it appears that in a shared Excel workbook, charts are cannot be selected.  Hence when I select the range that includes the table and the chart, only the table is selected (and subsequently copied and pasted) when the workbook is shared.  Can anyone share code that would copy a chart and paste it in another workbook?  Thanks for the help.

    Doug Pruiett


    • Edited by Doug Pruiett Wednesday, October 19, 2016 8:11 PM
    Wednesday, October 19, 2016 8:03 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, October 20, 2016 5:54 AM
  • Hi Doug,

    This is the limitation of Excel.

    you need to find work around for your issue.

    if workbook is shared then you can try to save as it and create a temporary copy of it. then copy the contents and delete the file.

    or you can try to unshare the workbook then you can able to copy the data. so you need to first unshare it copy the data and again share it. but other users are affected by doing this and they can loss their work.

    Code for remove sharing.

    Sub RemoveSharing(  )
        If ThisWorkbook.MultiUserEditing Then _
          ThisWorkbook.ExclusiveAccess
    End Sub

    code for save the copy of file

    Sub SaveCopyAs(  )
        fil = ThisWorkbook.Path & "\" & "Copy of " & _
          ThisWorkbook.Name
        ThisWorkbook.SaveAs fil, , , , , , xlExclusive
    End Sub
    you need to add this code to workbook before sharing.

    because when workbook is shared then no one able to access and changed the code.

    but macro can be run when workbook is shared.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 21, 2016 8:48 AM
    Moderator