Macro

Hi

We receive raw data via email in excel 2010 several times a day. We go through the same process with each data file so we used a macro to record the process. Our macro only ever works on the original sheet it was recorded on despite it being saved in our personal workbook. We can see where the error is when we debug "runtime error 9", but we dont know what steps to take to resolve the isssue.

Each workbook we receive has already been named with our clients reference, therefore when we try and run the macro on another clients data (whom has a different client reference) the macro comes with the error.

The code below is the error we receive (ActiveWorkbook.Worksheets("1241014").Sort.SortFields.Clear), the problem is the ("1241014") as each workbook has been sent to us with a different number. I hope you can help as I'm near pulling out the little hair I have left :).

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Cells.Select
Cells.EntireColumn.AutoFit
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("C:F").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Desc"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Out"
Range("D1").Select
ActiveCell.FormulaR1C1 = "In"
Columns("A:D").Select
ActiveWorkbook.Worksheets("1241014").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1241014").Sort.SortFields.Add Key:=Range("A2:A621" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1241014").Sort
.SetRange Range("A1:D621")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub

Thanks
Lee