I am trying to record the macro step by step using record macro function. I am able to record the macro upto step 2. Step 3 when resizing, it selects columns D:N whereas it has to select only Column D as many rows as selected in cell J. This is the macro I have recorded.
Option Explicit
Sub newmacro()
'
' newmacro Macro
'
'
Dim x As String, y As String
Range("E2:F2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
Range("A2").Select
ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Add2 Key:=Range( _
"G2:G21"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Add2 Key:=Range( _
"J2:J21"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Canara Bank").Sort
.SetRange Range("A1:K21")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("J2").Select
Range(Selection, Selection.End(xlDown)).Select
y = Sheets("Canara Bank").Range("D2").CurrentRegion.Rows.Count
x = Sheets("Canara Bank").Range("J2:J" & Sheets("Canara Bank").Range("J" & Rows.Count).End(xlDown).Row).Rows.Count
Range("D2").Select
Range("D2").Resize(x, y).Copy
Range("D2").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub