Macro VBA code

[RAJESH1960]

This is a Sample Bank Statement with additional columns inserted. I follow the below steps manually to complete the entries. I am trying to do create a macro where all the steps are included in one macro. A bit challenging but not for the experts to write this code. 1. Sort the data by Voucher Type, Add Level, Credit 2. Count the number of rows in Credit - J2:J5 2. Count the same number of rows in Particulars, copy - D2:D5 4. Paste the copied rows and paste in Bank / Cash column - E2:E5 5. And also enter Canara Bank(Cell K1) in column Ledger Name as many rows - F2:F5 6. In column Bank/Cash in the balance rows enter Canara Bank till the end. - E6:E21 7. In Ledger Name the remaining rows, copy the data from Column Particulars copy the data till the end and paste it in the column of Ledger Name - F6:F21 Note: The name of the bank wil not be the same but will be mentioned in column K1 The number of entries in Contra also may be different in other banks The statement may have more than 1000-1500 entries. I have sent a raw statement for reference as well as the final statement after doing the above steps

 

[sykes]

If you want a project writing for commercial use, I would suggest one of the consulting companies will do this.

These are just a few which are mentioned on Mr. Excel:

 

[RAJESH1960]

If you want a project writing for commercial use, I would suggest one of the consulting companies will do this.

These are just a few which are mentioned on Mr. Excel:

It is not for commercial use. With new ideas and with the help of experts I am trying to create different projects. My interest to create is only because you people are here to guide and solve the questions. I am just doing for fun and once successful I upload the videos for others to use it for free.

 

[RAJESH1960]

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

 

[RAJESH1960]

Case Solved