Friday, August 31, 2012

How to Add Column Headings As Worksheets in Excel with VBA

VBA Macro to Add Column Headings As Worksheets in Excel
Sub Mac_Headings()
Dim x As Integer

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

End Sub

How to Trim Data and Delete Duplicate Records in Excel with VBA

VBA Macro to Delete Duplicate Records In a Specified Column in Excel
Sub Trim_Del()
Dim A As Integer
Dim B As Integer
Dim c As Integer
Dim d As Integer
Dim x As Integer

On Error GoTo Label:
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

Label:
End Sub

Thanks,Tamatam

How to extract a Search Item from One Workbook to another Workbook in Excel with VBA

How to Pull Out a Search Item from One Workbook to Another
Sub Search_Extract()
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:\MyDocuments\" & B
Workbooks(B).Close
End Sub

Thanks,Tamatam

How to Extract All Search Item Records from One Sheet to Another Sheet in Excel with VBA

VBA Macro to Pull Out All Search Item Records From One Sheet to Another Sheet
Sub Search_Extract()
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

Thanks,Tamatam

How to Extract a Search Item from a Range and Move to a Desired Column in Excel with VBA

VBA Macro to Pull Out a Search Item from a Range and Move to the Desired Column in Excel
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

Thanks,Tamatam

How to Extract Search Item from a Column and Move to Desired Column in Excel with VBA

VBA Macro to Pull Out Search Item From a Column and Move to a Desired Column in Excel
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

Thanks,Tamatam

How to Copy Entire Column to another Column in Excel with VBA

VBA Macro to Copy Entire Column to another Column in Excel
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

--Using Do-Until method
Sub ColShuffle_DoUntil()
Dim x
x = 0
Do Until x = 20
x = x + 1
Selection.Offset(x, 0) = Selection.Value
Loop
End Sub

How to Add the desired Number of Sheets in Excel with VBA

VBA Macro to Add the desired No.of Sheets in Excel

Sub Add_Shts()
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 for 'n' no.of times.

How to Auto Fill a value for Specified Number of Times in Same Column using VBA

VBA Macro to Auto Fill a Specified Number of Times In Same Column
Sub Mac_Fill()
Dim A as Integer, 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


 

How to Add, Delete, Rename Sheets In Active WorkBook using VBA Macro

VBA Macro To Add, Delete, Rename Sheets In a Active WorkBook
Sub AddDelRenam()
Dim x as Integer, y as Integer
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 WorkBook

Macro To Know The Available Worksheet Names In a Active WorkBook
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

How to Insert the desired No.of Rows and Columns In a Active Sheet using VBA

VBA Macro to Insert the desired No.of Rows and Columns In a Active Sheet
Sub InsertRows()
Dim i as Integer, j as Integer
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

Thanks,Tamatam

How to Find the Count of No.of Sheets In a Active WorkBook using VBA

VBA Macro To Find Count of No.Of Sheets In A Active WorkBook
Sub Count_Shts()
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 Count_Rows_Columns()
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

Thanks,Tamatam

How to Delete Selected Cells, Rows, Columns In a Active WorkSheet using VBA Macro

VBA Macro To Delete Selected Cells, Rows, Columns In a Active WorkSheet 
Sub Delete_Cells( )
Selection.Delete  'Deletes Selected Cells'
End Sub

Sub Delete_Row( )
rows(1).select
Selection.EntireRow.Delete  'Deletes Entire Row'
End Sub

Sub Delete_Column( )
columns(2).select
Selection.EntireColumn.Delete  'Deletes Entire Column'
End Sub

Thanks, Tamatam

Excel VBA Macro to Clear Contents, Formats of an Active WorkSheet

VBA Macro to Clear Contents, Formats of an Active WorkSheet 
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

Thanks,Tamatam



How To Change the Text Case in Excel using VBA Macro

Macro To Change Text Case in Excel
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

Thanks,Tamatam

How to Select and Set Values to a Range in Excel with VBA

VBA Macro to Select and Set Values to a Range in Excel
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

Thanks,Tamatam

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

Thanks,Tamatam

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog