+ Reply to Thread
Results 1 to 3 of 3

"run time error 9 subscript out of range" Help?

  1. #1
    Registered User
    Join Date
    06-24-2014
    Location
    Illinois
    MS-Off Ver
    2013
    Posts
    2

    "run time error 9 subscript out of range" Help?

    I have a macro that is giving me the out of range error starting with the line: " ActiveWorkbook.Worksheets("DWM - Daily Transactions.xlsx").Sort.SortFields.Clear"
    I have very little experience with macros, so the debug isn't of any help. From what I gather online, it's a problem with the file name. The file it's being run in is: DWM - Daily Transactions.xlsx which seems to match exactly. Can anyone help? TIA!!!

    Here's the complete code:

    Sub DailyDownload()
    '
    ' DailyDownload Macro
    '
    ' Keyboard Shortcut: Ctrl+d
    '
    Cells.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlThin
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlThin
    End With
    Columns("K:N").Select
    Selection.Style = "Comma"
    Columns("C:C").EntireColumn.AutoFit
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("D1").Select
    ActiveWorkbook.Worksheets("DWM - Daily Transactions.xlsx").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DWM - Daily Transactions.xlsx").Sort.SortFields.Add Key _
    :=Range("J2:J40"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
    ActiveWorkbook.Worksheets("DWM - Daily Transactions.xlsx").Sort.SortFields.Add Key _
    :=Range("A2:A40"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
    ActiveWorkbook.Worksheets("DWM - Daily Transactions.xlsx").Sort.SortFields.Add Key _
    :=Range("B2:B40"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
    With ActiveWorkbook.Worksheets("DWM - Daily Transactions.xlsx").Sort
    .SetRange Range("A1:P40")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.25)
    .RightMargin = Application.InchesToPoints(0.25)
    .TopMargin = Application.InchesToPoints(0.25)
    .BottomMargin = Application.InchesToPoints(0.25)
    .HeaderMargin = Application.InchesToPoints(0.3)
    .FooterMargin = Application.InchesToPoints(0.3)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
    .PrintErrors = xlPrintErrorsDisplayed
    .OddAndEvenPagesHeaderFooter = False
    .DifferentFirstPageHeaderFooter = False
    .ScaleWithDocHeaderFooter = True
    .AlignMarginsHeaderFooter = True
    .EvenPage.LeftHeader.Text = ""
    .EvenPage.CenterHeader.Text = ""
    .EvenPage.RightHeader.Text = ""
    .EvenPage.LeftFooter.Text = ""
    .EvenPage.CenterFooter.Text = ""
    .EvenPage.RightFooter.Text = ""
    .FirstPage.LeftHeader.Text = ""
    .FirstPage.CenterHeader.Text = ""
    .FirstPage.RightHeader.Text = ""
    .FirstPage.LeftFooter.Text = ""
    .FirstPage.CenterFooter.Text = ""
    .FirstPage.RightFooter.Text = ""
    End With
    ChDir "J:\Junxure\CompanyFiles\DAILY DOWNLOAD--2388\DailyDownloads"
    ActiveWorkbook.SaveAs Filename:= _
    "J:\Junxure\CompanyFiles\DAILY DOWNLOAD--2388\DailyDownloads\change to date.xlsx" _
    , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End Sub

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: "run time error 9 subscript out of range" Help?

    This is expecting the name of the worksheet; not the workbook.
    ActiveWorkbook.Worksheets("DWM - Daily Transactions.xlsx")


    If you just want to reference the currently active sheet...
    ActiveSheet.Sort.SortFields.Clear
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    06-24-2014
    Location
    Illinois
    MS-Off Ver
    2013
    Posts
    2

    Re: "run time error 9 subscript out of range" Help?

    THANK YOU AlphaFrog! That makes sense. I changed all the instances of "DWM - Daily Transactions.xlsx" to "CustomReport" which is the worksheet name and it works!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Breaking out Comma Delimited Fields - VBA Error - "Subscript out of Range"
    By sev979 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2014, 09:55 AM
  2. Replies: 1
    Last Post: 12-15-2011, 10:59 AM
  3. Error msgs: "Object varible or with block variable not set"; "subscript out of range"
    By menyanthe in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-26-2009, 04:58 PM
  4. [SOLVED] "Subscript out of range" error for: Workbooks("Test1.xls").Save
    By Just12341234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2005, 11:05 AM
  5. FileCopy Command Giving "Subscript Out of Range" Error Message
    By Jim Hagan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2005, 02:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1