Some time we
have a huge date in our excel workbook and we need to work on that but before
we start our job, data has to be set on a particular order.
So for that
there are some basic VBA coding to make your work easy.
Case1:-
Your Data type
Name: Amit
|
Salary: 19786
|
designation: MBA VA
|
company: Alfa overseas
|
Name: Manish
|
Salary: 18835
|
designation: Team leader
|
company: Alfa overseas
|
Name: Sunil
|
Salary: 16466
|
designation: Admin VA
|
company: Alfa overseas
|
Name: Navneet
|
Salary: 13520
|
designation: MBA VA
|
company: Alfa overseas
|
And to make
this data easy and look like
Amit
|
19786
|
MBA VA
|
Alfa overseas
|
Manish
|
18835
|
Team leader
|
Alfa overseas
|
Sunil
|
16466
|
Admin VA
|
Alfa overseas
|
Navneet
|
13520
|
MBA VA
|
Alfa overseas
|
Step 1:- Press
Alt+F11
Step 2:- select
ThisWorkbook
Step 3:-
copy the given code and paste it.
Step 4:-
press F5.
You are done
with your data.
Note:- data should
be in sheet1 and correct data will be display in sheet2.
Sub
excelmacro()
Sheets("sheet1").Select
Range("A1").Select
Sheets("sheet2").Select
Range("A2").Select
For i = 1 To
1
Sheets("sheet1").Select
If
Len(ActiveCell.Value) > 1 Then
xname = Right(ActiveCell.Value,
Len(ActiveCell.Value) - 6)
xsalary = Right(ActiveCell.Offset(0,
1).Value, Len(ActiveCell.Offset(0, 1)) - 8)
xdesig = Right(ActiveCell.Offset(0,
2).Value, Len(ActiveCell.Offset(0, 2)) - 13)
xcomp = Right(ActiveCell.Offset(0,
3).Value, Len(ActiveCell.Offset(0, 3)) - 9)
Sheets("sheet2").Select
ActiveCell.Value = xname
ActiveCell.Offset(0, 1).Value = xsalary
ActiveCell.Offset(0, 2).Value = xdesig
ActiveCell.Offset(0, 3).Value = xcomp
ActiveCell.Offset(1, 0).Select
Sheets("sheet1").Select
ActiveCell.Offset(1, 0).Select
Else
i = 10
End If
i = i - 1
Next
End Sub
Case 2:-
Your data type
Name: Amit
|
Salary: 19786
|
designation: MBA VA
|
company: Alfa overseas
|
Name: Manish
|
Salary: 18835
|
designation: Team leader
|
company: Alfa overseas
|
Name: Sunil
|
Salary: 16466
|
designation: Admin VA
|
company: Alfa overseas
|
Name: Navneet
|
Salary: 13520
|
designation: MBA VA
|
company: Alfa overseas
|
And to make
this data easy and look like
Amit
|
19786
|
MBA VA
|
Alfa overseas
|
Manish
|
18835
|
Team leader
|
Alfa overseas
|
Sunil
|
16466
|
Admin VA
|
Alfa overseas
|
Navneet
|
13520
|
MBA VA
|
Alfa overseas
|
Follow the above mentioned steps 1 and 2.
Step 3:- copy the given code and paste it.
Step 4:- press F5.
You are done
with your data.
Note:- data should
be in sheet 1 and correct data will be display in sheet 2.
Sub excelmacro()
Sheets("sheet1").Select
Range("A1").Select
Sheets("sheet2").Select
Range("A2").Select
For i = 1 To 3
Sheets("sheet1").Select
If Len(ActiveCell.Value) > 1 Then
xname =
Right(ActiveCell.Value, Len(ActiveCell.Value) - 6)
xsalary =
Right(ActiveCell.Offset(1, 0).Value, Len(ActiveCell.Offset(1, 0)) - 8)
xdesig =
Right(ActiveCell.Offset(2, 0).Value, Len(ActiveCell.Offset(2, 0)) - 13)
xcomp =
Right(ActiveCell.Offset(3, 0).Value, Len(ActiveCell.Offset(3, 0)) - 9)
Sheets("sheet2").Select
ActiveCell.Value = xname
ActiveCell.Offset(0, 1).Value
= xsalary
ActiveCell.Offset(0, 2).Value
= xdesig
ActiveCell.Offset(0, 3).Value
= xcomp
ActiveCell.Offset(1,
0).Select
Sheets("sheet1").Select
ActiveCell.Offset(4,
0).Select
Else
i = 10
End If
i = i - 1
Next
End Sub
