Runtime error '9' Subscript out of range

[mickinho]

Hi

I have copied my macro below.

While I am planning on recording a macro to do more than simply what is below I have played around with the macros for a couple of weeks now (I am a complete beginner) and are continuing to get the following error.

Runtime error '9' subscript out of range

I wish to import data lists that will be different everytime (but will directly import into the same columns) and sort this data alphabetically using a macro. It appears that because the length of the data list changes each time the macro is trying to reference a cell that doesn't really exist???

Any help would be greatly appreciated.

Thanks

Mick


Sub Pickingslip()
'
' Pickingslip Macro
'

'
Range("E13:H362").Select
ActiveWorkbook.Worksheets(" Patient History Report from 14").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets(" Patient History Report from 14").Sort.SortFields. _
Add Key:=Range("F13:F110"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(" Patient History Report from 14").Sort
.SetRange Range("E13:H110")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

[protonLeah]

You can get a "subscript out of range" error when the sheet you are trying to work with does not exist, or, if it does exist, the name in the macro does not match the name in the sheet tab. For instance, you have a space before Patient that looks suspect. But you didn't post a sample workbook

[mickinho]

You are right protonLeah. Thanks for the quick reply.

When I am exporting my original data into the spreadsheet it is automatically naming the first sheet ' Patient History Report from XX' where XX is the first part of the date that the report is covering(in the format DD/MM/YY).

When I then run another report from a different beginning date I get the error. As I don't have anyway to specify how the exported report names the first sheet do you have any other suggestions?

I have attached a sample workbook that WILL NOT show the error as the Macro was originally recorded using this data so the first sheet name is used in the macro. But if you change the first sheet name to " Patient History Report from XX' where "XX = anything other than 14" you will get the error.

Thanks

Mick

S100 Picking Slip.XLS

[event21]

Hi -

If you're always doing the sorting in the first sheet, then

Please Login or Register  to view this content.

event