excel - Working macro returns error when run from form control

Question

I'm trying to move all charts from source sheet to destination sheet. The following code works fine when I run it from the VB Editor. My source had two charts and both were successfully moved.

But when I try to run it using a form button from a third sheet (neither source nor destination), it only works for the first chart and then returns an error. Infuriatingly it retains a copy of the 'moved' chart in the source as well.

Sub moveAllCharts(source As String, destination As String)
    Dim chartObject As Object
    
    ActiveWorkbook.Worksheets.Add(After:=Application.Worksheets(source)).Name = destination
    For Each chartObject In Worksheets(source).ChartObjects
        chartObject.Chart.Location xlLocationAsObject, destination
    Next chartObject
End Sub

The code gets stuck inside the loop and returns a The specified dimension is not valid for the current chart type error.

Answer 1

Like I said in comments, this is a very old problem which can be only sorted (I hope I am wrong) by activating the source sheet and selecting the chart. If you do not do that, you will end up with The specified dimension is not valid for the current chart type Chart. error

Is this what you are trying?

Sub moveAllCharts(source As String, destination As String)
    Dim chartObject As Object
    
    ActiveWorkbook.Worksheets.Add(After:=Application.Worksheets(source)).Name = destination
    
    For Each chartObject In Worksheets(source).ChartObjects
        '~~> Need both of these
        Worksheets(source).Activate
        chartObject.Select
        
        ActiveChart.Location xlLocationAsObject, destination
    Next chartObject
End Sub

BTW, since you are forced to use .Activate and .Select, the above code should also include error handling. For example

Sub moveAllCharts(source As String, destination As String)
    Dim chartObject As Object
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets(source)
        
    On Error GoTo Whoa
        
    wb.Worksheets.Add(After:=wb.Worksheets(source)).Name = destination
        
    For Each chartObject In ws.ChartObjects
        '~~> Need both of these
        ws.Activate
        chartObject.Select
            
        ActiveChart.Location xlLocationAsObject, destination
    Next chartObject
LetsContinue:
    Exit Sub
Whoa:
    MsgBox err.Description
    Resume LetsContinue
End Sub

In Action