0

Hi I have this macro and a few others that are similar that struggle when the worksheet names include "-", " ", "(" or ")". It normally fails on line when the graph series is created ActiveChart.FullSeriesCollection(1).XValues = "=" & ws.Name & "!$G$61:$G$" & iBas In the debugger when I hover over ws.Name it says Automation Error.

I have got around this by changing the worksheet names to remove these characters but I would like to understand why it fails and what if anything I can do to leave the worksheet names as they are?

Sub COREStepChart()
'
' Insert additional rows to have x value for base of core run in order to create bar/step shaped chart by depth/elevation and update graph series to new array

' Macro created by Dan Brenton 20200814

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
 
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strRef, strRange As String
    Dim iRow, iBase, iTCR, iSCR, iRQD, iDepthBase, iOrder As Integer
    Set wb = ThisWorkbook
    For Each ws In wb.Worksheets
        Select Case ws.Name
        Case "Template", "Report", "Configuration (CORE)", "Configuration (Moisture)"
        Case Else
        ws.Name = Replace(ws.Name, "-", "")
        ws.Name = Replace(ws.Name, " ", "")
        ws.Name = Replace(ws.Name, "(", "")
        ws.Name = Replace(ws.Name, ")", "")
        ws.Activate
            iTop = 61
            iBase = 62
            iLoca = 2
            iDepthTop = 5
            iDepthBase = 6
            iTCR = 7
            iSCR = 8
            iRQD = 9
            iOrder = 12
            iElev = 17
            
            Do While ws.Cells(iTop, iTCR) <> ""

                    Rows(iBase & ":" & iBase).Select
                    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                    Range(Cells(iTop, iLoca), Cells(iTop, iElev)).Select
                    Selection.Copy
                    Cells(iBase, iLoca).Select
                    ActiveSheet.Paste
                    Cells(iTop, iDepthBase).Select
                    Selection.Copy
                    Cells(iBase, iDepthTop).Select
                    ActiveSheet.Paste
                    Cells(iTop, iOrder).Select
                    ActiveCell.FormulaR1C1 = "1"
                    Cells(iBase, iOrder).Select
                    ActiveCell.FormulaR1C1 = "2"
                    iTop = iTop + 2
                    iBase = iBase + 2
                            
            Loop
                    
            ActiveSheet.ChartObjects("Chart2").Activate
            ActiveChart.PlotArea.Select
            ActiveChart.FullSeriesCollection(1).XValues = "=" & ws.Name & "!$G$61:$G$" & iBase
            ActiveChart.FullSeriesCollection(1).Values = "=" & ws.Name & "!$Q$61:$Q$" & iBase
            ActiveChart.FullSeriesCollection(2).XValues = "=" & ws.Name & "!$H$61:$H$" & iBase
            ActiveChart.FullSeriesCollection(2).Values = "=" & ws.Name & "!$Q$61:$Q$" & iBase
            ActiveChart.FullSeriesCollection(3).XValues = "=" & ws.Name & "!$I$61:$I$" & iBase
            ActiveChart.FullSeriesCollection(3).Values = "=" & ws.Name & "!$Q$61:$Q$" & iBase
        
            ActiveSheet.ChartObjects("Chart5").Activate
            ActiveChart.PlotArea.Select
            ActiveChart.FullSeriesCollection(1).XValues = "=" & ws.Name & "!$G$61:$G$" & iBase
            ActiveChart.FullSeriesCollection(1).Values = "=" & ws.Name & "!$E$61:$E$" & iBase
            ActiveChart.FullSeriesCollection(2).XValues = "=" & ws.Name & "!$H$61:$H$" & iBase
            ActiveChart.FullSeriesCollection(2).Values = "=" & ws.Name & "!$E$61:$E$" & iBase
            ActiveChart.FullSeriesCollection(3).XValues = "=" & ws.Name & "!$I$61:$I$" & iBase
            ActiveChart.FullSeriesCollection(3).Values = "=" & ws.Name & "!$E$61:$E$" & iBase
                    
                    
        End Select
        
    Next
    
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
    
   End Sub
Dan
  • 95
  • 1
  • 13
  • Is it because of spaces in sheet names perhaps? If you have a space the name has to be in single quotes `'`. – SJR Aug 14 '20 at 11:07
  • 1
    @SJR: wouldn't that be the case in formulas where you have to use `'`? if OP is looping through each sheet (without any reference to the sheet name), i would have thought that shouldn't be an issue? @Dan: please have a read of [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). I suspect part of your problem is using things like `Select` and `Activate` – Zac Aug 14 '20 at 11:24
  • 1
    @SJR: Sorry just read where OP is trying to set `XValues` and `Values`. I suspect you are absolutely correct with your `'` comment – Zac Aug 14 '20 at 11:27
  • Sorry so do you suggest changing the code to 'ws.Name' ? – Dan Aug 14 '20 at 11:36
  • `Dim strRef, strRange As String` doesn't mean what you think it means. It is equivalent to `Dim strRef As Variant, strRange As String` rather than `Dim strRef As String, strRange As String`. Similar remarks hold for your `Integer` declarations (which would be better as `Long` anyway -- why risk overflow?) – John Coleman Aug 14 '20 at 11:41
  • 1
    You would need `"'" & ws.Name & "'"` if you want to insert single quotes around the name. – John Coleman Aug 14 '20 at 11:46
  • Thanks @John Coleman. Changing ws.Name to this has done the trick. ActiveChart.FullSeriesCollection(1).XValues = "=" & "'" & ws.Name & "'" & "!$G$61:$G$" & iBase. Please can you explain why the ws.Name needs the single quotes? – Dan Aug 14 '20 at 12:14
  • That is just the Excel syntax of using worksheet names in formulas when the worksheet names contain spaces. – John Coleman Aug 14 '20 at 12:15

0 Answers0