The specified Dimension is not valid for the current chart type

[mshbhwn98]

Hi,

I have some code which loops through a range called rngAnalysisList and assigns value to variables. The range is a table list analyses. The code then copies a template results page ready to fill out. The results page has 3 graphs and a table on it.

I am getting the following error

The specified Dimension is not valid for the current chart type

When it gets to copying the template for the second time.

Here is my code

Private Sub test()
    Dim i As Integer, j As Integer
    Dim myRange As Range
    Dim ImportTypeVal As String, EDMVal As String, AccVal As String, IDVal As String
    Application.ScreenUpdating = False
 
    Set myRange = wksModeller.Range("rngAnalysisList")
    For i = 1 To myRange.Rows.Count
       For j = 1 To myRange.Columns.Count
          If j = 1 Then
             IDVal = myRange.Cells(i, j).Value
          ElseIf j = 5 Then
             ImportTypeVal = myRange.Cells(i, j).Value
          ElseIf j = 6 Then
             EDMVal = myRange.Cells(i, j).Value
          ElseIf j = 7 Then
             AccVal = myRange.Cells(i, j).Value
          Else
          End If
       Next j
    
    Sheets("Template").Copy After:=Sheets("Modeller")
    ThisWorkbook.VBProject.VBComponents("wksTemplate1").Name = "wksAnalysis" & IDVal
    Sheets("Template (2)").Name = "Analysis" & IDVal
    
    Next i
    

    Application.ScreenUpdating = True
End Sub

Why would this be causing this error?

Thanks

[mshbhwn98]

I have now solved this problem. It turns out there is nothing wrong with the code (with respect to it working). It may be poorly written as I'm a newbie. But the IDVal taken from the source table was not unique hence the error. Apologies for wasting anyones times.

[SamT]

Look at this version and see what you think

Private Sub test()
    'Integer will not handle all the rows on a sheet
    'and forces VBA to convert it to a Long when using a Range
    Dim i As Long
    Dim ImportTypeVal As String, EDMVal As String, AccVal As String, IDVal As String
    
    Application.ScreenUpdating = False
     
    With Sheets("Modeller").Range("rngAnalysisList")
      For i = 1 To .Rows.Count
          IDVal = .Cells(i, 1).Value
          ImportTypeVal = .Cells(i, 5).Value
          EDMVal = .Cells(i, 6).Value
          AccVal = .Cells(i, 7).Value
           
          Sheets("Template").Copy After:=Sheets("Modeller")
          If Not IDVal = "" Then Sheets(Sheets.Count).Name = "wksAnalysis" & IDVal
      Next i
    End With
    Application.ScreenUpdating = True
End Sub

[mshbhwn98]

Apologies. I appreciate you taking the time to reply. I thought this part of my post

"which loops through a range called rngAnalysisList and assigns value to variables. The range is a table list analyses. The code then copies a template results page ready to fill out"

sufficiently explained what I was trying to do. So to avoid the same problem in the future how would I explain what I am trying to do? I am very greatful so I hope this comes across in the nicest possible way

Thanks

[SamT]

That is what that code does.
Loop thru each Row in Range(rngAnalysisList
with each Row (i) assign the Variables values by certain columns
For each Row, Copy the Template and name it according to the Column (1) and Row (i) of rngAnalysis.

It only looks different because I have a different style than you. Where you loop thru all the columns in each row of MyRange. I see that the same columns of myRange are used all the time.

Since myRange = Sheets("Modeller").Range("rngAnalysisList"), I just used Range("rngAnalysisList") directly

[mshbhwn98]

That's great. Thank you. I actually miss read the bit at the bottom of your post (see below) so was confused because i thought I had been clear.

"Please take the time to read the Forum FAQ
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H."

I do have another question regarding the same bit of code. It copies the 3 charts when the sheet is copied and the charts have names chart 1, chart 2 and chart 3. Why would the new copied sheet have graphs called chart 2, chart 2 and chart 3? So chart 1 on the new sheet is now also called chart 2. I reference the charts in another piece of code to resize them so the code breaks because it cannot find Chart 1 on the new copied sheet.

Thanks for all your help

[SamT]

I suspect that those are not the actual Names, ie they might be (Names.) Confused yet.

Open VBA and make sure you can see the Project Explorer (press Ctrl+R) and the Properties Window (Press F4.)

Click on any Chart Object in the Project Explorer and look at the Name(Name) pair of the Chart.
now look at the Properties Window and look at the (Name) Property and the Name Property. Confused Again?

Going back to the Project explorer Window, The Name(Name) pair is actually the CodeName(Tab Name) pair. Every Chart, Worksheet and Workbook have Code Names and common names. Common names are visible in Excel. You can only see Code names in VBA. If you reference an object with
Sheets("Sheet1") you are referencing its Tab Name. But Suppose Sheets("Sheet1") has the Code Name Sheet3? (It happens a lot.)

You can actually reference one of those objects with its Code Name. In this example (Sheets("Sheet1") or Code Name Sheet3)

With Sheet3

BTW, Workbooks don't have a "Tab" or Common Name until they are saved, but all workbooks have the Code Name "ThisWorkbook."

OK, Back to the Properties Window. In this window you can change both Names of any of those Objects. To change the Code Name of an Object, click the Box next to (Name) and enter a new Code Name. To change the Common or "Tab Name, click the Box next to Name (No Brackets) and enter the new name. I always refer to the Names in the Project explorer to know what I am doing with the names.

Coders refer to the common name as "Name" and to the Code Name as "Code Name."

Remember all that. Write it on the back of your hand.

I hope this helps you find the naming issue with your Charts.

[mshbhwn98]

Thanks SamT. I'm not following the instructions you've given me. So I have the Project Explorer and Properties Windows already open. The project explorer does not have any chart objects in it though. (I cannot see any). All I can see is the Sheets and ThisWorkbook. I also have a couple of Modules but that's it.
screenshot.jpg

Am I missing something?

Thanks

[Bob Phillips]

I do have another question regarding the same bit of code. It copies the 3 charts when the sheet is copied and the charts have names chart 1, chart 2 and chart 3. Why would the new copied sheet have graphs called chart 2, chart 2 and chart 3?

Probably you have embedded charts on a worksheet.

[mshbhwn98]

Does that mean they do not have codenames?

How do I change the name of the embeded Chart if this is the case? My code looks like this

Private Sub MoveCharts()
Application.ScreenUpdating = False
    
    Dim top As Integer
    
    If Rows("14:33").Hidden = True And Columns("F:R").Hidden = True Then
        top = 270
    Else
        top = 600
    End If
    
    ActiveSheet.Shapes("Chart 1").top = top
    ActiveSheet.Shapes("Chart 1").Left = 20
    ActiveSheet.Shapes("Chart 1").Height = 300
    ActiveSheet.Shapes("Chart 1").Width = 380
    
    ActiveSheet.Shapes("Chart 2").top = top
    ActiveSheet.Shapes("Chart 2").Left = 412
    ActiveSheet.Shapes("Chart 2").Height = 300
    ActiveSheet.Shapes("Chart 2").Width = 380
    
    ActiveSheet.Shapes("Chart 3").top = top
    ActiveSheet.Shapes("Chart 3").Left = 804
    ActiveSheet.Shapes("Chart 3").Height = 300
    ActiveSheet.Shapes("Chart 3").Width = 380
Application.ScreenUpdating = True
End Sub

So when the copy sheet is made from the first sub calling this sub fails as there is no longer a "Chart 1" available.

Many thanks