VBA Code previously working okay but not now, works relatively okay in Debug though??!

[StuLythgoe]

Hi
Sorry about this, I've listed this previously on a different post but I think it wasn't clear what my problem was and the resolution I was aiming for, so if it's a faux par to re-post trying to clarify an original then I apologise in advance. I wrote a procedure a month or so ago to open a worksheet created by VBA using Cognos Impromptu which is then opened by a seperate procedure which is shown below as Old Procedure. It then adds a chart to Sheet2 created from data on Sheet1. When I originally wrote the procedure (using Office 2003 on Windows Server 2003 at work) the procedure worked great and just as I expected it to. More recently as I have been learning more about VBA I have been trying to refine my code and make it more efficient at what it does and to clear out any code that isn't necessary, I've also tried to add some very basic error handling into the procedure to prevent the front end user getting any VBA error messages. Now when I run the procedure it works as intended apart from not changing the font sizes of the labels on the created chart (It changes the orientation and adds value labels to the chart and the line of code to change the font size is in the middle of the code that effects the changes that have worked)

With ActiveChart.Axes(xlValue).TickLabels<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
.AutoScaleFont = True<o></o>
.Font.Size = 8<o></o>
End With<o></o>
With ActiveChart.Axes(xlCategory).TickLabels<o></o>
.AutoScaleFont = True<o></o>
.Font.Size = 8<o></o>
End With<o></o>
<o></o>
ActiveChart.Axes(xlValue).MajorGridlines.Delete<o></o>
ActiveChart.Legend.Delete<o></o>
<o></o>
ActiveChart.SeriesCollection(1).ApplyDataLabels ShowValue:=True<o></o>
With ActiveChart.SeriesCollection(1).DataLabels<o></o>
.AutoScaleFont = True<o></o>
.Font.Size = 8<o></o>
.Orientation = xlUpward
<o> End With</o>


I then decided to try to debug my code to see what was going on. When I run upto the cursor just before the problem lines and then step in line by line, it makes the changes as the older version of the code did, but when I get down to the line of code to insert a row into the sheet on which the Chart is stored

Sheets("Sheet2").Select<o></o>
Rows("1:1").Select<o></o>
Selection.Insert Shift:=xlDown<o></o>
Rows("1:1").RowHeight = 21<o></o>


I get Run-time error '1004': Method 'Rows' of object '_global' failed for a line of code that I have recreated with the Macro recorder and which works fine outside of this procedure. I think I may through inexperience have coded something in earlier which is causing this clash or not declared something that I should have done. I'm just looking for some assistance to try and figure out what is going wrong and why the original version worked, but now after what I consider to be some tweaking and refining why it's ended up not working properly. (I have also tested this procedure on my own laptop using Windows 7 and Excel 2003 professional and I get a compounded version of the problem (It actually gives me the Method 'Rows' of object '_global' failed as a runtime 1004 error during the running of the procedure as compared to during debug) but if I run it on Windows 7 using Excel 2010 it works as the original procedure worked applying all the formatting as expected.)

Original Working version of procedure (apart from the run time 1004 error also found in this procedure during debugging)

Sub Maintenance()
'
[/FONT][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana]    Dim Book1 As String
    Dim EndCell As String
    Dim TotalRange As String
    Dim TotalRange2 As String
    Dim Path As String
    Dim StartTime As Double
    
    Path = InputBox("Enter details of path to folder containing report - (eg. S:\Stocks & Systems\Systems\Monday Reports\280811", "Path to File?")
    
    Book1 = Path & "\Component Summary.xls"
            
    StartTime = Timer
        
    Workbooks.Open Filename:=[Book1]
        
    Range("A1").Select
    Do
        ActiveCell.Offset(1, 0).Range("A1").Select
    Loop Until ActiveCell.Value = 0
        ActiveCell.Offset(-1, 1).Range("A1").Select
        EndCell = ActiveCell.Range("A1").Address
        TotalRange = "B2" & ":" & EndCell
        TotalRange2 = "A1" & ":" & EndCell
                   
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Total Cost"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*1"
    Range("B2").Select
    Selection.Autofill Destination:=Range(TotalRange)
    Range(TotalRange).Select
    Columns("A:C").Select
    Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Columns("B:B").Select
    Selection.ColumnWidth = 9.6
    Columns("C:C").Select
    Selection.EntireColumn.Hidden = True
    
    Range(TotalRange2).Select
    Range(EndCell).Activate
    Sheets("Sheet1").Select
    Sheets.Add
    Sheets("Sheet1").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(TotalRange2), PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Total Cost"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    ActiveSheet.Shapes("Chart 1").IncrementLeft -219#
    ActiveSheet.Shapes("Chart 1").IncrementTop -141.75
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.95, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleHeight 1.93, msoFalse, msoScaleFromTopLeft
    
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.AutoScaleFont = True
    With Selection.TickLabels.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    ActiveChart.Axes(xlCategory).Select
    Selection.TickLabels.AutoScaleFont = True
    With Selection.TickLabels.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    Selection.Delete
    ActiveChart.Legend.Select
    Selection.Delete
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
        False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
        ShowPercentage:=False, ShowBubbleSize:=False
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        .Position = xlLabelPositionOutsideEnd
        .Orientation = xlUpward
    End With
    
    Sheets("Sheet2").Activate
    Sheets("Sheet2").Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Rows("1:1").RowHeight = 21
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Arial"
        .Size = 12
    End With
    Rows("1:1").EntireRow.AutoFit
    Range("A1:S1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .MergeCells = True
    End With
    
    ActiveCell.FormulaR1C1 = "Sheet Creation automated using VBA in Excel (in " & Format(Timer - StartTime, "00.00") & " seconds)"
              
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    
End Sub

Rewritten (Newest) version of the procedure<o></o>

Sub Maintenance()
'
[/FONT][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana]    Dim Path As String
    Dim Book1 As String, EndCell As String, TotalRange As String, TotalRange2 As String
    Dim StartTime As Double
    
    Path = InputBox("Enter details of path to folder containing report - (eg. S:\Stocks & Systems\Systems\Monday Reports\280811", "Path to File?")
    
        If Path = "" Then Exit Sub
            
    Book1 = Path & "\Component Summary.xls"
                           
    Application.ScreenUpdating = False
                           
    StartTime = Timer
    
    On Error GoTo 1:
        
    Workbooks.Open Filename:=[Book1]
    
1:
        If Err.Number = "1004" Then Exit Sub
                   
    Range("A:A").End(xlDown).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
        EndCell = ActiveCell.Range("A1").Address
        TotalRange = "B2" & ":" & EndCell
        TotalRange2 = "A1" & ":" & EndCell
                   
    Columns("B:B").Insert Shift:=xlToRight
    Range("B1").Value = "Total Cost"
    Range("B2").Value = "=RC[1]*1"
    Range("B2").AutoFill Destination:=Range(TotalRange)
    Columns("A:C").Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Columns("B:B").ColumnWidth = 9.6
    Columns("C:C").EntireColumn.Hidden = True
    
    Range(TotalRange2).Select
    Range(EndCell).Activate
    Sheets.Add
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(TotalRange2), PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Total Cost"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    ActiveSheet.Shapes("Chart 1").IncrementLeft -219#
    ActiveSheet.Shapes("Chart 1").IncrementTop -141.75
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.95, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleHeight 1.93, msoFalse, msoScaleFromTopLeft
    
    With ActiveChart.Axes(xlValue).TickLabels
        .AutoScaleFont = True
        .Font.Size = 8
    End With
    With ActiveChart.Axes(xlCategory).TickLabels
        .AutoScaleFont = True
        .Font.Size = 8
    End With
            
    ActiveChart.Axes(xlValue).MajorGridlines.Delete
    ActiveChart.Legend.Delete
    
    ActiveChart.SeriesCollection(1).ApplyDataLabels ShowValue:=True
    With ActiveChart.SeriesCollection(1).DataLabels
            .AutoScaleFont = True
            .Font.Size = 8
            .Orientation = xlUpward
    End With
        
    Sheets("Sheet2").Activate
    Sheets("Sheet2").Rows("1:1").Insert Shift:=xlDown
    Sheets("Sheet2").Rows("1:1").RowHeight = 21
    
    Sheets("Sheet2").Rows("1:1").Font.Bold = True
    Sheets("Sheet2").Rows("1:1").Font.Name = "Arial"
    Sheets("Sheet2").Rows("1:1").Font.Size = 12
    Sheets("Sheet2").Rows("1:1").EntireRow.AutoFit
    Range("A1:S1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .MergeCells = True
    End With
    
    ActiveCell.FormulaR1C1 = "Sheet Creation automated using VBA in Excel (in " & Format(Timer - StartTime, "00.00") & " seconds)"
              
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    
    Application.ScreenUpdating = True
    
End Sub

<o></o>

 

[StuLythgoe]

I assume that if no reply has been received to my post it means that no-one on the entire MrExcel Forum is able to assist me with this??