Friday, 31 August 2012

Macro To Add Column Headings As Worksheets

Macro To Add Column Headings As Worksheets

Sub MAC4()
Dim x As Integer

For x = 1 To 5
Sheets.ADD.Name = Sheets("AAA").Cells(1, x)
Next x

End Sub

Excel VBA Macro To Trim Data And Delete Duplicate Records

Macro To Delete Duplicate Records In a Specified Column
Sub TrimDel()
Dim A As Integer
Dim B As Integer
Dim c As Integer
Dim d As Integer
Dim x As Integer

On Error GoTo TPR:

B = InputBox("Enter Column No. To Check For Duplicates:")

For A = 1 To 100
Cells(A, B).Offset(0, 1) = Application.Proper(Trim(Replace(Replace(Replace (LCase(Cells(A, B)),  ".", " "), ",", " "), "  ", " ")))

Next A

For c = 100 To 1 Step -1
For d = 100 To 1 Step -1
If d <> c Then

If Cells(d, B).Offset(0, 1) = Cells(c, B).Offset(0, 1) Then
x = x + 1

If x > 2 Then
Cells(c, B).Offset(0, 1).Delete shift:=xlUp
Application.ScreenUpdating = False

End If
End If
End If

Next d
Next c

TPR:

End Sub

Macro To Pull Out a Search Item From One Book To Another

Macro To Pull Out a Search Item From One Book To Another

Sub SearchPull1()
Dim x As Integer
Dim Y As Integer
Dim Z As Integer
Dim A As Integer
Dim B As String
Dim c As String
A = 1
c = InputBox(" Enter Search String ")
B = InputBox(" Enter Work Book Name To Save ")

Workbooks.ADD.SaveAs Filename:=B
Workbooks.Open Filename:=B
Sheets.ADD.Name = c

For Z = 1 To 26
For x = 1 To 100

If InStr(LCase(Workbooks("A").Sheets("AAA").Cells(x, Z)), LCase(c)) Then
A = A + 1

For Y = 1 To 26

Workbooks(B).Sheets(c).Cells(A, Y) = Workbooks("A").Sheets("AAA").Cells(x, Y)
Workbooks(B).Sheets(c).Cells(1, Y) = Workbooks("A").Sheets("AAA").Cells(1, Y)
Cells(1, Y).Font.Bold = True

Next Y
End If
Next x
Next Z

Workbooks(B).SaveAs Filename:="D:\SAS\" & B
Workbooks(B).Close

End Sub

Macro To Pull Out All Search Item Records From One Sheet To Another

Macro To Pull Out All Search Item Records From One Sheet To Another

Sub SearchPull()
Dim x As Integer
Dim Y As Integer
Dim Z As Integer
Dim A As Integer
Dim c As String
A = 1
c = InputBox(" Enter Search String ")
Sheets.ADD.Name = c
For Z = 1 To 26
For x = 1 To 100

If InStr(LCase(Sheets("AAA").Cells(x, Z)), LCase(c)) Then
A = A + 1

For Y = 1 To 26
Sheets(c).Cells(A, Y) = Sheets("AAA").Cells(x, Y)
Sheets(c).Cells(1, Y) = Sheets("AAA").Cells(1, Y)
Next Y

End If
Next x
Next Z
End Sub

Macro To Pull Out Search Item From A Range And Move To Desired Column


Macro To Pull Out Search Item From A Range  And Move To Desired Column


Sub SearchMultipleCol()
Dim x As Integer
Dim Y As Integer
Dim Z As Integer
Dim A As Integer
Dim c As String

c = InputBox("Enter Search String")
Z = InputBox("Enter Results Column No.")

For Y = 1 To 5

For x = 1 To 100
If InStr(LCase(Cells(x, Y)), LCase(c)) Then

A = A + 1
Cells(A, Z) = Cells(x, Y)

End If

Next x
Next Y

End Sub

Macro To Pull Out Search Item From A Column And Move To Desired Column

Macro To Pull Out Search Item From A Column  And Move To Desired Column

Sub SerchinCol()
Dim x As Integer
Dim Y As Integer
Dim Z As Integer
Dim B As Integer
Dim A As String
Dim d As String

B = 1
d = InputBox("Enter Your Name: ")
A = InputBox("Hai" & " " & d & " " & "Enter Your Serch Sring :")
Y = InputBox("Enter Serch Colno :")
Z = InputBox("Enter Serch Results Colno :")

Cells(1, Z) = " Serach Results "
Cells(1, Z).Font.Bold = True

For x = 1 To 100
If InStr(LCase(Cells(x, Y)), LCase(A)) Then
B = B + 1
Cells(B, Z) = Cells(x, Y)
End If

Next x
End Sub

Macro To Copy Entire Column To Another Column

Macro To Copy Entire Column  To Another Column 

Sub ColShuffle()
Dim x As Integer
Dim Y As Integer
Dim Z As Integer

Y = InputBox("Enter Column No. To Move ")
Z = InputBox("Enter Destination Column No.")
For x = 1 To 100
Cells(x, Z) = Cells(x, Y)
Next x
End Sub

Sub duntill()
Dim x
x = 0
Do Until x = 20
x = x + 1
Selection.Offset(x, 0) = Selection.Value
Loop
End Sub

Macro To Add Desired No.Of Sheets

Macro To Add Desired No.Of Sheets

Sub ADD()
Dim x, Y
x = InputBox("Enter The No.Of Sheets To Insert :")
For Y = 1 To x
Sheets.ADD
Next Y
End Sub

Note:
This is a very user friendly macro that allows you to add any no.of sheets whose number is increased automatically[sheet1,sheet2,sheet3...Sheetn],even you run a macro 'n' no.of times.

Macro To Auto Fill A Specified Number Of Times In Same Column



Macro To Auto Fill A Specified Number Of Times In Same Column

Sub FILL()
Dim A, B As Integer

A = InputBox("Enter No.of Times to Fill:")

For B = 1 To A - 1
Selection.Offset(B, 0) = Selection.Value

Next B
End Sub


 

Macro To Add,Delete,Rename Sheets In Active Work Book


Macro To Add,Delete,Rename Sheets In  Active Work Book


Sub AddDelRenam()

Dim x, Y
x = InputBox("Enter The Sheet Name To Add :")
Sheets.ADD.Name = x

Y = InputBox("Enter The Sheet Name To Delete :")
Sheets(x).Delete

A = InputBox("Enter The Sheet Name To Rename :")
B = InputBox("Enter The New Name To Sheet :" & A)
Sheets(A).Name = B

End Sub

Macro To Know The Available Worksheet Names In A Active Work Book

Macro To Know The Available Worksheet Names In A  Active Work Book

Sub ShowWorkSheets()
Dim x As Worksheet
For Each x In Worksheets
MsgBox ("The Available Worksheets In This Workbook Are :") & vbCrLf & x.Name
Next x
End Sub

Macro To Insert Desired No.Of Rows And Columns In A Active Sheet


Macro To Insert Desired No.Of Rows And Columns In A  Active  Sheet


Sub InsertRows()
Dim i, j

i = InputBox("Enter The No.of Rows Required ")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(i - 1, 0)).Select
Selection.EntireRow.Insert

j = InputBox("Enter The No.of Columns Required ")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, j - 1)).Select
Selection.EntireColumn.Insert

End Sub

Macro To Find Count of No.Of Sheets In A Active Work Book

Macro To Find Count of No.Of Sheets In A Active Work Book

Sub CountS()
Mycount = Application.Sheets.COUNT
MsgBox Mycount
End Sub


Macro To Count No.Of Rows And Columns Selected In A Active Sheet

Macro To Count No.Of Rows And Columns Selected  In A Active Sheet

Sub Count1()
Dim Count1, Count2 As Variant
Count1 = Selection.Rows.COUNT
Count2 = Selection.Columns.COUNT
MsgBox ("No.of Rows = ") & Count1 & vbCrLf & ("No.of Coloumns = ") & Count2 & vbCrLf & ("Existed In The Current Selection ")

End Sub

Macro To Delete Selected Cells , Rows, Columns In A Active Work Sheet


Macro To Delete Selected Cells , Rows, Columns In A Active Work Sheet 


Sub Delete1( )
Selection.Delete                     'Deletes Selected Cells'
End Sub

Sub Delete2( )
rows(1).select
selection.EntireRow.Delete  ''Deletes Entire Row'
End Sub

Sub Delete2( )
columns(2).select
selection.EntireColumn.Delete  ''Deletes Entire Column'
End Sub




Excel VBA Macro To Clear Contents,Formats Of A Active Work Sheet

Macro To Clear Contents,Formats Of A Active Work Sheet 
--------------------------------------------------------------------------------------
Example-I: 

Sub Clear_Contents()

'Removes only the cell contents

Range("A1:Z1000").ClearContents

End Sub
--------------------------------------------------------------------------------------
Example-II: 
Sub Clear_All()
'To Remove cell contents & their formats
Range("A1:Z1000").Clear

End Sub



Excel VBA Macro To Change Text Case

Macro To Change Text Case
Macro To Change Text To Upper Case
Sub Upper()
For Each x In Range("A1:Z1000")
x.Value = UCASE(x.Value)
Next
End Sub



Macro To Change Text To Lower Case
Sub lower()
For Each x In Range("A1:Z1000")
x.Value = LCase(x.Value)
Next
End Sub

Macro To Change Text To Proper Case
Sub Proper()
For Each x In Range("A1:Z1000")
x.Value = Application.Proper(x.Value)
Next
End Sub

Macro To Select And Set Values To A Range

Macro To Select And Set Values To A Range

Sub MAC()
Range("A1:A10").Select 'Select The Range'
End Sub

'Macro To Set A Value To A Range'
Sub MAC1()
Range("A1:A10") = 10 'Range Value Is Set To 10'
End Sub

'Macro To Set Values To Columns'
Sub MAC2()
Columns(c) = 15 'Column C Value is Set To 15'
Columns("D:E") = 20 'Column D,E Values are Set To  20'
End Sub


'Macro To Set  Values To Rows'
Sub MAC3()
Rows(5) = 25 'Row 5 Value Set To 25'
Rows("5:7") = 30 'Rows 5,7 Values are Set To 30'
End Sub

Excel VBA Macro To Add, Open, Save A Workbook

Macro To Add, Open, Save A Workbook
Sub Add_Workbook()
Workbooks.ADD.SaveAs Filename:="C:\Documents and Settings\Administrator\My Documents\Test.xlsx" 'Creates A Workbook
Workbooks("A").Worksheets("AAA").Range("A1:Z65000").Copy

Workbooks.Open Filename:="C:\Documents and Settings\Administrator\My Documents\Test.xlsx" 'Opens An Existing Workbook
Workbooks("Test").Activate
Workbooks("Test").Worksheets("sheet1").Range("a1").PasteSpecial
Workbooks("Test").Save
Workbooks("Test").Close
End Sub

Wednesday, 15 August 2012

How to Display or Hide the Formulas in a Worksheet

How to see the Formula in a Cell itself without going for Formula Address Bar
Suppose we have data and formulas in a sheet. If you want to see the formula without going to Address bars , simply you can put one space before the formula then you can see the formula in the cell it self as shown below :


If you want Display or Hide all the formulas in a sheet , you can use the following short cut key :

Ctrl + ` ( Control+ Apostrophe/Grave Accent)  



How to create a Hyper link from Excel to a specific Section of a Word Document

How to create a Hyper link from Excel to a specific Section of a Word Document in other Location
If you want to create a hyperlink from your Excel to a  particular context/section in some page of a word document , you have do the following things.

>> First open the Word document
>> Next select desired text for which you have to create a book mark for hyper link as follows.



>> Here I have Defined a Bookmark as P_Summary for Professional Summary section.
>> After defining the Bookmark Name save and close the word document.

Note: 
Bookmark name does not allow spaces ,numbers, special characters except underscore.

>> Now open the Excel sheet and select a cell where want insert the hyperlink.
>> Next select the file from the hyper link dialogue box to create link.

>> Now add the Bookmark name following by ‘#’ to the Filename in the Address bar shown below.

Ex: Temp\Resum.doc#P_Summary

>> In the Text to Display address bar you can give your desired name.


Now click ‘OK’  you will see a desired hyper link as shown below.


Thanks.,
TAMATAM






How to Convert Text to Columns in Excel

How to Convert Text to Columns using Wizard in Excel
Conversion of Text Columns refers to separation of  simple cell content in to different columns.
We can done by using Text to Columns wizard as explained in following example.

Example:
Suppose column A having full names(Name and Surname) of the employees whose Name and Surnames are separated by a  particular delimiter(such as “”,’;:._-) and if you want Name and Surname in different columns say B and C then you can do in the following manner.

>> First go to Data >> Text to Columns.
>>Next  mark the Delimited Option and click Next> button as  shown below.



>> Now mark all delimiter types such as  Comma, Space, Semicolon, Tab and select Other if there is any other delimiter existed between the names and specify the other delimiter in the given other delimiter box. As shown below.


 
>> Now press Next> button.
>> Next select the Destination where you want the result.



>>Now Click Finish, then you will see the result as Name and Surname In different columns as shown below.



Thanks.,
TAMATAM

How to get Time value from a cell having Date and Time

Get Time value from a cell having 'Date and Time' in Excel
Suppose cell A2 have a date and time , from that if you want only Time in B2 then simply use the following formula.

=TIME(HOUR(C1),MINUTE(C1),SECOND(C1))

Example:





Thanks,
TAMATAM

How to get Date value from a cell having Date and Time

Get Date value from a cell having Date and Time in Excel
Suppose cell A2 have a date and time but if you want only date in B2 then simply use the following formula.

=DATE(YEAR(C1),MONTH(C1),DAY(C1))

Example :



Thanks,
TAMATAM

Monday, 13 August 2012

Excel TRIM Function Syntax and Example

Excel TRIM Function:
Trim function trims/removes all trailing, leading and additional spaces with in the text , but keeps one space between the words with in text.

Syntax:

=TRIM(TEXT)

Example:  
Explained in the screen shot.



Thanks,
TAMATAM

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts