Working macro returns error when run from form control

Walser52

New Member
Joined
Jul 19, 2021
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Obviously I cannot replicate your setup, but I think maybe you just need to pay more attention to your worksheet references to explicitly tie them to the same workbook object. See if this works.

VBA Code:
Sub moveAllCharts(source As String, destination As String)
    Dim chartObject As Object

    With ActiveWorkbook
        .Worksheets.Add(After:=.Worksheets(source)).Name = destination
        For Each chartObject In .Worksheets(source).ChartObjects
            chartObject.Chart.Location xlLocationAsObject, destination
        Next chartObject
    End With
End Sub
 
Upvote 0
Obviously I cannot replicate your setup, but I think maybe you just need to pay more attention to your worksheet references to explicitly tie them to the same workbook object. See if this works.

VBA Code:
Sub moveAllCharts(source As String, destination As String)
    Dim chartObject As Object

    With ActiveWorkbook
        .Worksheets.Add(After:=.Worksheets(source)).Name = destination
        For Each chartObject In .Worksheets(source).ChartObjects
            chartObject.Chart.Location xlLocationAsObject, destination
        Next chartObject
    End With
End Sub

This still gives the same error but someone at StackOverflow made the same suggestion as you and it worked.


VBA Code:
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
 
Upvote 0
For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Whoops. Sorry. And yes I did read the link at the end.

Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top