charts/graphs - paste special as pictures

[ajm]

I have a macro that distributes my 10 meg master file into a file per customer. I am trying to reduce the size of the final output and wish to copy the charts on each tab and paste them as pictures in the customer's own output file. can anybody assist please?

Sub CUSTOMDIFOT()

'/// this macro takes a master workbook and splits it out by specific Customers in C4
'/// relies on project sheets to be bound by "First" and "Last" worksheets (as bookends)


    Dim Sourcewb As Workbook
    Dim cstartt As Integer, cendd As Integer
    Dim i As Integer
    Dim fname As String, WklyPFTfolder As String, WklyPFTname As String, altfolder As String
    Dim MasterValsOnly As String, MasterValsOnlyName as string
    Dim c As Variant
    Dim WKNBR As Long

    With Application
        .DisplayAlerts = False
        ' .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set Sourcewb = ThisWorkbook
    
    WKNBR = Sourcewb.Worksheets("TREF").Range("P3")
    
    fileextstr = ".xlsm": FileFormatNum = 52

     'WklyPFTfolder = "I:\05 Commercial\JIMMY G\WEEKLY REPORTS\Weekly PFT"
    altfolder = "C:\Documents and Settings\andremou\My Documents"

MasterValsOnly = "TREF Weekly DIFOT Report - " & " Wk" & WKNBR
MasterValsOnlyName = altfolder & "\" & MasterValsOnly & fileextstr

'///save values only copy
Sourcewb.SaveCopyAs Filename:=MasterValsOnlyName

 With Workbooks.Open(MasterValsOnly)
                Application.EnableEvents = True

            With ActiveWorkbook
'///hardcode all formulas in new workbook

        For Each ws In Worksheets
            ws.Cells.Copy
            ws.[A1].PasteSpecial Paste:=xlValues
            ws.Cells.Hyperlinks.Delete
            '.CutCopyMode = False '///runtime error 438 Object doesn't support prop or method
            '.Goto ws.Cells(1, 1) '///runtime error 438 Object doesn't support prop or method
        Next ws
        
        '///ensure bookend sheets remain hidden
          .Sheets("First").Visible = False
        .Sheets("Last").Visible = False
                    
                Application.EnableEvents = False
                
                    .Close savechanges:=True
                    
            End With
End With

    '///come back to source file

    For Each c In Range("customsheets") '///list of customers needing reports

        If c.Value = "" Then GoTo AfterArrays

            fname = "TREF Weekly DIFOT Report - " & c & " Wk" & WKNBR
           
            
            WklyPFTname = altfolder & "\" & fname & fileextstr

        Sourcewb.SaveCopyAs Filename:=WklyPFTname

            With Workbooks.Open(WklyPFTname)
                Application.EnableEvents = True

            With ActiveWorkbook

        For Each ws In Worksheets
            ws.Cells.Copy
            ws.[A1].PasteSpecial Paste:=xlValues
            ws.Cells.Hyperlinks.Delete
            '.CutCopyMode = False
            '.Goto ws.Cells(1, 1)
        Next ws

                    cstartt = Sheets("First").Index + 1
                
                    cendd = Sheets("Last").Index - 1
              
                '///loop backwards through sheets. avoids problem with "Last" sheet being deleted
                        For i = cendd To cstartt Step -1
                 
                            If Sheets(i).Range("C4") <> c Then '/// C4 on each tab holds cust name
                                Sheets(i).Delete
                            End If
                        Next i
                    .Sheets("First").Visible = False
                    .Sheets("Last").Visible = False
                    
                Application.EnableEvents = False
                
                    .Close savechanges:=True
                    
            End With
            End With
        
    Next c


AfterArrays:

    With Application
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

this is very sloopy code as i am trying to piece it together from separate projects.

the layout of each page is a table at top and then chart below.

the code generated files end up as 7mg. once these files have been created, i then have to manually open each one and remove data pertaining to other customers and weeks outside our current view (10 weeks at a time). this part of the operation took 5 minutes per file yesterday. and only resulted in reduction from 7mg to a couple hundred kbs per file.

perhaps I should do the cull of data first? how do i write that into my existing code?