Trying to Sort and Subtotal with Vbscript in Excel
 

I'm using VBScript and my output creates a worksheet with 7 columns
and I would like to sort and subtotal column 7 or G. I'm using the
below code and get an error at the subtotal row. I've search all over
Google and Microsoft, tried several different variations, no luck. I
have also put a message in the vbscript group.

objExcel.worksheets(Var1).Activate
' Set the sort key to be G
objExcel.Columns("G:G").Select
Set Key1 = objExcel.Range("G2")
objExcel.Columns("A:G").Sort Key1,1

' Subtotal where column G changes and count rows
objExcel.Columns("A:G").Subtotal 7, xlCount, Array(7), True, False,
True

I get the error Subscript out of range: '[number: 7]. code 800A0009

I can post more of the code, if needed.

Trying to Sort and Subtotal with Vbscript in Excel
 

The following worked fine for me:

Sub aaSubtotal()
Set objExcel = Application
Var1 = "Sheet3"
objExcel.Worksheets(Var1).Activate
' Set the sort key to be G
objExcel.Columns("G:G").Select
Set Key1 = objExcel.Range("G2")
objExcel.Columns("A:G").Sort Key1, 1, Header:=True
' Subtotal where column G changes and count rows
objExcel.Columns("A:G").Subtotal 7, xlCount, Array(7), True, False, True

End Sub

the only thing I added was an argument to the sort to specify that the data
had headers (it should).

--
Regards,
Tom Ogilvy

"Jeremy Smith" wrote in message
om...
I'm using VBScript and my output creates a worksheet with 7 columns
and I would like to sort and subtotal column 7 or G. I'm using the
below code and get an error at the subtotal row. I've search all over
Google and Microsoft, tried several different variations, no luck. I
have also put a message in the vbscript group.

objExcel.worksheets(Var1).Activate
' Set the sort key to be G
objExcel.Columns("G:G").Select
Set Key1 = objExcel.Range("G2")
objExcel.Columns("A:G").Sort Key1,1

' Subtotal where column G changes and count rows
objExcel.Columns("A:G").Subtotal 7, xlCount, Array(7), True, False,
True

I get the error Subscript out of range: '[number: 7]. code 800A0009

I can post more of the code, if needed.