Hi,
Can someone help me work out why my code keeps failing?
I have a sheet called Individual and this has 20 graphs on the page, each one named in order from Chart 1 to Chart 20.
On a separate tab, named Chart Select, there are four drop down boxes for the user to select up to 4 charts from the 20 displayed in the individual tab.
When the user selects the chart they want from the drop down lists, Cells P20 to P23 uses a Vlookup to select the correct chart name to copy / paste.
When the user clicks the Update button - with the macro code assigned to it - the following code should copy and paste the relevant charts into the relevant cells and then resizes each one so they fit neatly on the page ready to print out in A4.
On running the macro, I am receiving a Runtime Error 1004 - Application defined or object-defined error.
Some of the charts will have pasted accross, but not all - which is odd - for example selecting:
option 1: Chart 1
option 2: Chart 2
option 3: Chart 3
option 4: Chart 4
The first two charts will appear before the code fails attempting to copy/paste Chart 3.
If I change all options to only Chart 1 or Chart 2, the code updates all charts as expected
If I select chart 3 as option 1 in the first dropdown selection, the code fails straight away
The strange thing is, if I then click into the Individual tab and select "Home > Find & Select > Selection Pane... " - which brings up the list of all my charts - and then re-run my macro, the charts update as expected! - regardless of the chart name and position in the selection list!
Hope that makes sense!
Hope someone has some idea of what is happening...
Here is the code i am using:
Can someone help me work out why my code keeps failing?
I have a sheet called Individual and this has 20 graphs on the page, each one named in order from Chart 1 to Chart 20.
On a separate tab, named Chart Select, there are four drop down boxes for the user to select up to 4 charts from the 20 displayed in the individual tab.
When the user selects the chart they want from the drop down lists, Cells P20 to P23 uses a Vlookup to select the correct chart name to copy / paste.
When the user clicks the Update button - with the macro code assigned to it - the following code should copy and paste the relevant charts into the relevant cells and then resizes each one so they fit neatly on the page ready to print out in A4.
On running the macro, I am receiving a Runtime Error 1004 - Application defined or object-defined error.
Some of the charts will have pasted accross, but not all - which is odd - for example selecting:
option 1: Chart 1
option 2: Chart 2
option 3: Chart 3
option 4: Chart 4
The first two charts will appear before the code fails attempting to copy/paste Chart 3.
If I change all options to only Chart 1 or Chart 2, the code updates all charts as expected
If I select chart 3 as option 1 in the first dropdown selection, the code fails straight away
The strange thing is, if I then click into the Individual tab and select "Home > Find & Select > Selection Pane... " - which brings up the list of all my charts - and then re-run my macro, the charts update as expected! - regardless of the chart name and position in the selection list!
Hope that makes sense!
Hope someone has some idea of what is happening...
Here is the code i am using:
Code:
Sub ChartUpdate()
Application.ScreenUpdating = False
ActiveSheet.Unprotect "password"
Dim choice1, choice2, choice3, choice4 As String
choice1 = Range("P20").Value
choice2 = Range("P21").Value
choice3 = Range("P22").Value
choice4 = Range("P23").Value
'delete all existing charts on page
For Each chart In ActiveSheet.ChartObjects
chart.Delete
Next chart
'copy new charts
Sheets("Individual").ChartObjects(choice1).Copy
Range("B5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Individual").ChartObjects(choice2).Copy
Range("J5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Individual").ChartObjects(choice3).Copy
Range("B7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Individual").ChartObjects(choice4).Copy
Range("J7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'resize each new chart
For Each chart In ActiveSheet.ChartObjects
chart.Activate
chart.Height = Application.CentimetersToPoints(9)
chart.Width = Application.CentimetersToPoints(12.15)
Next chart
Range("B3").Select
Application.CommandBars("Selection and Visibility").Visible = False
ActiveSheet.Protect "password"
Application.ScreenUpdating = True
