MACRO’S SAMPLE CODINGS

MACRO’S SAMPLE CODINGS

Sub Busy_Hour()

Dim cnt As Integer, Dim i as Integer

Sheets("Output Sheet").Select

Range("A2:X5000").ClearContents

Range("A1").Select

Sheets("h").Range("a2").Select

cnt = Range("b1").Value

For i = 1 To cnt

ActiveCell.Copy

Sheets("DATA").Range("U1").PasteSpecial

ActiveSheet.AutoFilterMode = False

Range("A1:T1").AutoFilter

    Range("D1").AutoFilter field:=4, 

Criteria1:=ThisWorkbook.Worksheets("DATA").Range("U1").Value, _

Operator:=xlAnd

Range("P1").AutoFilter field:=16, 

Criteria1:=ThisWorkbook.Worksheets("DATA").Range("V1").Value, _

Operator:=xlAnd

If Range("V1").Value = 0 Then

Call Zero_Data

End If   

If Range("V1").Value > 0 Then

    Call Not_Zero

End If

    Sheets("h").Select

    ActiveCell.Offset(1, 0).Select

Next i

Sheets("data").Range("A1:T1").AutoFilter

Sheets("h").Range("a1").Select

Sheets("Output Sheet").Range("A1").Select

MsgBox ("Reports in Compeleted in outputsheet please check")

End Sub

Sub Trail ()

Dim lastrow As Integer, Dim i As Integer

lastrow = Range("A1").Value

Range("a2").Select

ActiveCell.Copy

For i = 1 To lastrow

a = ActiveCell.Column

b = ActiveCell.Row

Cells(b + 1, a).Select

Selection.PasteSpecial

Next i

End Sub

Sub trail ()

Workbooks.Open Filename:="c:\data.xlsx"

Windows ("data").Activate

Range ("a1").Value = "himanshu"

Windows ("data").Close

End Sub

Dim rng as range

Set rng = selection

Sub trail()

Dim iret As Integer

iret = MsgBox(strPrompt, vbYesNo, strTitle)

 If iret = vbNo Then

     MsgBox "NO!"

    Else

    MsgBox "Yes!"

   End If

End Sub

Sub A_Main()

' A_Main Macro

    Dim Response As Integer

Sheets("BSC WISE").Select

    Range("C3:C22,H3:H19,M3:M15,R3:R7").Select

    Selection.ClearContents.

Response = MsgBox(prompt:="You want to making main report.So,select'Yes' or 'No'.", Buttons:=vbYesNo)

     If Response = vbYes Then   Exit Sub

    Call Report

    Call Total_data

    ElseifResponse = vbNo

Sheets("H").Select

    Range("A1").Select

    MsgBox ("!!! Task Complete!!!")

End  if

End  sub

DIRECTIONS TO END

Range(“A1”).Select    

Selection.End(xlToLeft).Select

    Range(Selection, Selection.End(xlToRight)).Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.SpecialCells(xlCellTypeVisible).Select

Find Blank And Switch The Cell(Column And Row)

Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _

        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

        False, SearchFormat:=False).Activate

    a = ActiveCell.Column

    b = ActiveCell.Row

    Cells(b, a).Select

Selection.End(xlDown).Offset(0, 1).Select

Activecell.offset(1,0).select

ActiveCell.Offset(-5, -1).Range("A1").Select

ActiveCell.Offset(-5, -1).Range("A1:B3").Select

Sub Macro2()

  On Error GoTo ErrorHandler

    ActiveCell.Offset(-5, -1).Range("A1").Select

    Exit Sub

ErrorHandler:

   MsgBox "You must start below Row 5"

End sub

Sub FillEmptyCells()

    Selection.CurrentRegion.Select

    Selection.SpecialCells(xlCellTypeBlanks).Select

    Selection.FormulaR1C1 = "=R[-1]C"

    Selection.CurrentRegion.Select

    Selection.Copy

    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

        False, Transpose:=False

    Application.CutCopyMode = False

End sub

For Any Alternate /Unfixed Value In Filter Criterias

ActiveSheet.AutoFilterMode = False

Range("A1:X1").AutoFilter

Range("A1:x1").AutoFilter field:=1, Criteria1:=ThisWorkbook.Worksheets("CONWAY").Range("B1").Value, _

Operator:=xlAnd

Range("A1:D1").AutoFilter field:=2, Criteria1:="=" & ">" & ThisWorkbook.Worksheets("CONWAY").Range("C1").Value 'greater than filter to Contract ("B2" as criteria)

Range("A1:x1").AutoFilter field:=3, Criteria1:="=" & "<" & ThisWorkbook.Worksheets("CONWAY").Range("C1").Value 'less than filter to Op Code ("C2" as criteria)       

For Any Pivot Feild Fixed Value Only

ActiveSheet.PivotTables("PivotTable2").PivotFields("Circle / Central Nodes"). _

ClearAllFilters

    ActiveSheet.PivotTables("PivotTable2").PivotFields("Circle / Central Nodes"). _

CurrentPage = "AP"

For Active Pivot Feild Alternate /Unfixed Value Only

Sheets("Sheet2").PivotTables("PivotTable2").PivotFields("Circle / Central Nodes").CurrentPage = Range("J1").Value

Alternate Cell & Value Find & Replaced Command

Sub Replaced ()

'

    Sheets("sheet2").Range("a:a").Copy

     Sheets("sheet1").Range("I1").PasteSpecial

    Dim Replaced As String

    Dim x As Integer

  

    Sheets("Sheet1").Select

    Range("I2").Select

  

For x = 1 To 499

    Replaced = ActiveCell.Value

        Sheets("sheet1").Select

        Cells.Replace What:=Replaced, Replacement:="", LookAt:=xlPart, _

        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

        ReplaceFormat:=False

ActiveCell.Offset(1, 0).Select

Next x

End Sub