Trouble with basic macro - removing and sorting columns

Question

Trying to remove columns and sort a simple export and I'm getting an error:

Run-time error '9': Subscript out of range

every time I run the basic macro on a second recently created workbook.

Sub ONCE()
'
' ONCE Macro
' export and prep an ONC list
'
' Keyboard Shortcut: Ctrl+e
'
    Range("A:A,B:B,F:F").Select
    Range("F1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("A:A").Select
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 8.29
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("C:C").Select
    ActiveWorkbook.Worksheets("QueueDetail_20160802_150124").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("QueueDetail_20160802_150124").Sort.SortFields.Add _
        Key:=Range("C1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("QueueDetail_20160802_150124").Sort
        .SetRange Range("A2:C35")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("D4").Select
End Sub

The original workbook had 6 columns, all I did to record the macro was remove 3 of the extra columns, auto fit each column (double clicking so that each bit of information bellow fit visually), and then try to sort the workbook by the last column (smallest to largest).

When running the macro on a new workbook an error message is generated, and the sort doesn't run on the last column. Removing the extra columns and spacing seems to work flawlessly however.

because in your new workbook you don't have any sheet called `QueueDetail_20160802_150124` — cyboashu, Aug 02 '16 at 22:28
Welcome to StackOverflow, Aquilam. Here are a few pointers to get you started: (1) [try to avoid the usage of select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) (2) [fully qualify your code](http://stackoverflow.com/documentation/excel-vba/1576/common-mistakes/5110/qualifying-references#t=201608022225226090252) and write `ThisWorkbook.WorkSheets("QueueDetail_20160802_150124").Columns("A:A").EntireColumn.AutoFit` instead of just `Columns("A:A").EntireColumn.AutoFit`. (3) cells get selected and sheets get activated (not selected). — Ralph, Aug 02 '16 at 22:28
cyboashu, how do I change the code to run in whichever open workbook I'm currently in? — Aquilam, Aug 02 '16 at 22:42

ale10ander · Answer 1 · 2016-08-02T23:01:55.387

0

Add the sheet "QueueDetail_20160802_150124" to your new book, or else use ActiveWorkbook.sheets(1).Sort.SortFields.Clear sheets(1) will work on the first sheet in the active notebook; activesheet will work on the presently selected sheet.

If you use debugging, you can see that this is the line on which your code fails.

edited Aug 02 '16 at 23:01

answered Aug 02 '16 at 22:30

ale10ander

942
5
22
42

Is there a way to write the code to highlight a column with a specific key-word heading (by a specific word in column A1, B1 etc.), then delete those columns? And, is there a way to save it permanently to excel without having the original workbook associated with it? I'm generating a report, removing the excess information, then printing a hard copy. The generated report document is deleted shortly after a hard copy is printed. @ralph – Aquilam Aug 06 '16 at 15:56
@Aquilam- I'm not sure what you mean by "save it permanently to excel". You could save the document as a PDF, if that would satisfy your requirement. As far as looking for a specific word, look into looping in VBA (you should be able to loop over each column, or over each sheet and look at each A1, B1 etc. If the cell.value matches the text you're looking for, you can use Columns(2).Select or .entirecolumn.select to select it. It's definitely possible to do what you want, but this site isn't a "code for you" place. Try an example, and ask another question when you get stuck. Good luck! :-) – ale10ander Aug 08 '16 at 02:07

Trouble with basic macro - removing and sorting columns

1 Answers1