Refine Macro code and error correction

[N Prakash]

Hi,

I am getting Compilation error in the Sub Sort_Combined_Sheets() Macro code in the highlighted line. Will some one help me in correcting / refining the code Sub club Combined() and Sub Sort_Combined_Sheets() codes please. (First it has to combine and the sort left to right.

Sub Combined()

'This macro code will put all individual sheet records in different columns in a new sheet.
Dim Cnt As Long
Dim Sht As Worksheet
Dim Shts As Long

Shts = Sheets.Count
Sheets.Add after:=Sheets(Shts)
Set Sht = Sheets(Shts + 1)
Sht.Name = "Combined"
For Cnt = 1 To Shts
Sheets(Cnt).Columns(1).Copy Sht.Columns(Cnt)
Next Cnt

End Sub
---------------------------------------------------------
Sub Sort_Combined_Sheets()
'
' This Macro will sort all combined sheets in a new Work sheet Combined as required by the user
'
Dim lngLast As Long

Rows("1:1").Select
Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[1]C,_SRT,2,FALSE)"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:AN1"), Type:=xlFillDefault
Range("A1:AN1").Select
lngLast = Range("A" & Rows.Count).End(xlUp).Row
ActiveWorkbook.Worksheets("Combined").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Combined").Sort.SortFields.Add Key:=Range("A1:AN1") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Combined").Sort
.SetRange Range("A1:AN1002")
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub


Thanks
Prakash

 

[N Prakash]

OK I got it. Thanks for all