excel vba save workbook is ommiting the graphs RRS feed

  • Question

  • My copy Sheets to new workbook is not coping the graphs, but all the data is fine?

        Set Dest = Application.Workbooks.Add(1)
        'Source.Sheets(Array(Source.Sheets(1).Name, Source.Sheets(2).Name)).Copy Before:=Dest.Sheets(1)
        ThisWorkbook.Sheets(Array _
            (Source.Sheets("CH 1").Name, _
            Source.Sheets("CH 2").Name, _
            Source.Sheets("CH 6").Name)) _
        .Copy before:=Dest.Sheets(1)

        With Dest
        .Sheets("CH 1").Name , _
        .Sheets("CH 2").Name, _
        .Sheets("CH 6").Name
        End With

    heads up

    Sunday, February 22, 2015 8:44 AM

All replies

  • Try the following code. Edited since initial posting. all three examples worked for me and copied both data and the graphs (charts)    

    Sub test1()
        Dim Source As Workbook
        Dim Dest As Workbook
        Set Source = ThisWorkbook
        Set Dest = Workbooks.Add
        Source.Sheets(Array("CH 1", "CH 2", "CH 6")).Copy Before:=Dest.Sheets(1)
    End Sub

    Sub test2()
        Dim Source As Workbook
        Dim Dest As Workbook
        Dim arr
        Set Source = ThisWorkbook
        Set Dest = Workbooks.Add
        arr = Array(Source.Sheets(1).Name, Source.Sheets(2).Name, Source.Sheets(3).Name)
        Source.Sheets(arr).Copy Before:=Dest.Sheets(1)
    End Sub

    Sub test3()
        Dim Source As Workbook
        Dim Dest As Workbook
        Set Source = ThisWorkbook
        Set Dest = Workbooks.Add
        Source.Sheets(Array(Source.Sheets(1).Name, _
                Source.Sheets(2).Name, _
                Source.Sheets(3).Name)) _
                .Copy Before:=Dest.Sheets(1)
    End Sub

    Regards, OssieMac

    • Edited by OssieMac Sunday, February 22, 2015 11:12 AM
    Sunday, February 22, 2015 11:00 AM
  • Thank you , but I got the same results with no charts. So I'm a little confused...Imagine that :). If I click on the embedded chart in Sheet1, I see all the options to edit the chart. However if I run this litte vba."MsgBox "Charts.Count= " & ThisWorkbook.Charts.Count"  it returns "0". But if I save as from Excel, it obviously saves the Wbk fine. Any suggestions?

    heads up

    Monday, February 23, 2015 4:05 AM
  • Something odd, if I use the tab can try and create a copy, the copy does not bring the chart along. I recreated the chart in a new wkb, and still can save it other than using the file save as command. I'm stumped...

    heads up

    Monday, February 23, 2015 5:35 AM
  • Suggest that you try a repair.

    Firstly if you have not already done so then turn the computer off (Don't just use re-start; turn off completely) and re-start the computer and see if it makes a difference.) 

    Google the following and you will find links to repair workbooks and/or Office. (Replace 2010 with your version of office)

    repair excel 2010

    Further than that I cannot help you.

    Regards, OssieMac

    Monday, February 23, 2015 6:51 AM
  • OssieMac, Thanks for working the boards. I did reset pc and office but its a mystery. I will look at trying to paste special with the "microsoft office drawing object" as that seems to work inside the workbook. I'll see what I can learn there. Unchartered waters. Thanks again

    heads up

    Monday, February 23, 2015 3:28 PM
  • Hi texastwostep,

    Is there any update for your problem? Have you got the solution yet?

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 9, 2015 5:21 AM