0

I'm trying to create my first VBA code to basically: -select a cell -sort the column by A-Z -pivot the data -create a line chart -re-size the chart to make it bigger

I'm getting a runtime error 9 message subscript out of range and it highlights the following line
ActiveWorkbook.Worksheets("advertiserConversionReport_3045").Sort.SortFields. _ Clear

What I did was to open another workbook and tried to run the macro on the data. Looking through the code (I'm not a developer) I'm seeing that the errors could be the reference to the initial workbook advertiserConversionReport_3045 and perhaps a previously set range for the pivot Range("A2:F97")

Sub ActionReport()
'
' ActionReport Macro
' This macro will pivot data from the action report and create a line chart to show     trending of credited conversions by day.
'
' Keyboard Shortcut: Ctrl+shift+a
'
Range("B1").Select
ActiveWorkbook.Worksheets("advertiserConversionReport_3045").Sort.SortFields. _
    Clear
ActiveWorkbook.Worksheets("advertiserConversionReport_3045").Sort.SortFields. _
    Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("advertiserConversionReport_3045").Sort
    .SetRange Range("A2:F97")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "advertiserConversionReport_3045!R1C1:R97C6", Version:=xlPivotTableVersion14) _
    .CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
    , DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Day")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Conversion Action")
    .Orientation = xlRowField
    .Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Credited Conversions"), _
    "Sum of Credited Conversions", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Conversion Action")
    .Orientation = xlColumnField
    .Position = 1
End With
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$3:$R$11")
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.6583333333, msoFalse, _
    msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.7065974045, msoFalse, _
    msoScaleFromTopLeft
ActiveWindow.SmallScroll Down:=-24
ActiveSheet.Shapes("Chart 1").IncrementLeft -38.25
ActiveSheet.Shapes("Chart 1").IncrementTop -81.75
End Sub`

Does anyone know how to fix this and execute the macro successfully?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Glenn Low
  • 143
  • 1
  • 4
  • 11

1 Answers1

1

You are getting that error because the "Activeworkbook" doesn't have the sheet called "advertiserConversionReport_3045".

Avoid using ActiveWorkbook. Create Objects and then work with them. You may want to see this link

Try something like this

Sub Sample()
    Dim thiswb As Workbook, wbNew As Workbook

    Set thiswb = ThisWorkbook

    '~~> Change as applicable
    Set wbNew = Workbooks.Open("C:\Sample.xlsm")

    With wbNew
        .Worksheets("advertiserConversionReport_3045").Sort.SortFields.Clear
    End With
End Sub
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • hey siddharth, i'm still quite unsure as to how to fix the code.. if I were to include C:\Sample.xlsm and "advertiserConversionReport_3045" into the code, wouldn't it revert back to the same error? – Glenn Low Sep 11 '13 at 06:32
  • Where is the sub `ActionReport()`? And where is the sheet `advertiserConversionReport_3045`? EDIT: `C:\Sample.xlsm` is an example. As mentioned in the post, you will have to amend it – Siddharth Rout Sep 11 '13 at 06:33
  • mmm still pretty unsure as to how to go about doing it. by the way, the set ranges in the code would also give me more problems right? – Glenn Low Sep 11 '13 at 06:38
  • You haven't answered my questions :) – Siddharth Rout Sep 11 '13 at 06:41
  • mmm really sorry about that. i'm trying hard to understand the code myself, given that I have no knowledge about coding.. sub ActionReport() I have no idea where it is.. while the advertiserConversionReport_3045 is a file stored as .xlms on the desktop – Glenn Low Sep 11 '13 at 07:00
  • Hmmm, now you have got me confused :) `advertiserConversionReport_3045` is the file name or the sheet name? Did you not write this code? – Siddharth Rout Sep 11 '13 at 07:07
  • mmm i didn't write the code. i used the macro recording tool. and i think you're right .. advertiserConversionReport_3045 is the sheet name. sorry – Glenn Low Sep 11 '13 at 07:30
  • So now what you have to do is replace `"C:\Sample.xlsm"` with the file which has your data. Does this file have the sheet `advertiserConversionReport_3045`? – Siddharth Rout Sep 11 '13 at 07:47
  • hi Siddharth, the new file would have a new sheet name. is it possible to use just a generic 'activeworkbook' 'activesheet' kind of function? – Glenn Low Sep 11 '13 at 18:03
  • I've actually solved it - referring to http://www.mrexcel.com/forum/excel-questions/45119-rename-active-sheet-without-specifiying-name.html the solution would be to use ActiveSheet.Name = "my_new_name" at the start of the code so the code has a generic run for different workbooks having different sheet names. thanks siddharth for your help – Glenn Low Sep 11 '13 at 18:31