Excel VBA - Pivot Table Auto referesh

Assuming Your sheet name source data
1. Press ctrl+F3. Name Manager window will appear
2. Click on New
3. Define Name
4. in Refers to text box Enter below formula
=OFFSET('Source Data'!$A$1,0,0,COUNTA('Source Data'!$A:$A),6)
5. Click on ok
6.Now Press Alt + N + V + T short cut keys (or go to insert tab & select Pivot table)
7.Create Pivot table window will appear
8. in Table/Range box enter the name you define in 3 step & click on ok
9. Create Pivot table
10. Right click on pivot table sheet tab
11. click on View code
12. copy & paste below code in vb window ; change pivottable name

Private Sub Worksheet_Activate()
     'If this worksheet is activated, refresh the pivot table
     'Change "Pivot" to your sheet's name
     'Change "PivotTable1" to your pivot table's name

Sheets("Pivot").PivotTables("PivotTable1").RefreshTable
   
End Sub

13. Now whenever you change base data & select pivot table sheet it will get automatically refresh

Excel VBA - Pivot Table

You can record the steps of creating pivot table & generate pivot table
if you data range changes than you can use dynamic Range to create pivot table
Main Pivot table elements
PivotCaches :-
You can not see this.The PivotCache is a container that holds a static copy of the source data in memory
If you were to create a pivot table in VBA without using the wizard you'd need to address the pivot cache issue in code, like the following codeline which, if executed in the Immediate Window when the source data's sheet is active, would create a basic pivot table on that sheet on row 3, 2 columns to the right of the last used column (assuming the source data headers start in A1).

ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion).CreatePivotTable TableDestination:="R3C" & Range("A1").CurrentRegion.Columns.Count + 2

SourceData:
Normal Range : "Sheet1!R1C1:R7C6"
Dynamic Range : =OFFSET('Source Data'!$A$1,0,0,COUNTA('Sheet1'!$A:$A),6)

TableDestination : 
"Sheet1!R2C11" OR
ActiveSheet.Cells(3, 1)

along with this there are DefaultVersion,RowFields,PivotFields().Orientation

'To create Pivot table
Sub create_pivot()
Delete_All_Pivot_Tables_In_Sheet
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="'Pivot Table & VBA'!data").CreatePivotTable _
TableDestination:=ActiveSheet.Cells(10, 10), _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables(1).AddFields RowFields:="Name"
ActiveSheet.PivotTables(1).PivotFields("Name").Orientation = xlDataField
End Sub

'To delete all pivot tables
Sub Delete_All_Pivot_Tables_In_Sheet()
For Each pvt In ActiveSheet.PivotTables
    pvt.PivotSelect "", xlDataAndLabel, True
    Selection.ClearContents
Next pvt
End Sub

Excel : Why This Kolaveri Di_embed shockwave flash object

Within excel worksheet you can play videos

Steps:

1. Go to developer tab
2.  On the control group click on insert
3. Then click on more control (icon like hammer)
4. You will get More control box ; Select shockwave flash object & click ok
5. You will get cross sign (+) drag it on excel sheet & select the area for video
6.  Right click on square
7. Then select properties
8. Enter the full path to the Shockwave file (SWF) in the Movie property text-box. (e.g "C:\Users\MAHESH\Desktop\Download\Why This Kolaveri Di\Why This Kolaveri Di.swf")
9. Set the EmbedMovie property to True

2.  

Excel Convert Amount in words

You can use below UDF to convert amount in number to words.
copy below code in standard module
Formula will be =Spellword(A3)

Option Explicit
'Main Function
Function Spellword(ByVal MyNumber)
        Dim Temp
        Dim Rupees, Paisa As String
        Dim DecimalPlace, iCount
        Dim Hundreds, Words As String
        Dim place(9) As String
        place(0) = " Thousand "
        place(2) = " Lakh "
        place(4) = " Crore "
        place(6) = " Arab "
        place(8) = " Kharab "
        On Error Resume Next
        ' Convert MyNumber to a string, trimming extra spaces.
        MyNumber = Trim(Str(MyNumber))

        ' Find decimal place.
        DecimalPlace = InStr(MyNumber, ".")

        ' If we find decimal place...
        If DecimalPlace > 0 Then
            ' Convert Paisa
            Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
            Paisa = " and " & ConvertTens(Temp) & " Paisa"

            ' Strip off paisa from remainder to convert.
            MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
        End If

        '===============================================================
        Dim TM As String  ' If MyNumber between Rs.1 To 99 Only.
        TM = Right(MyNumber, 2)

        If Len(MyNumber) > 0 And Len(MyNumber) <= 2 Then
            If Len(TM) = 1 Then
                Words = ConvertDigit(TM)
                Spellword = "Rupees " & Words & Paisa & " Only"

                Exit Function

            Else
                If Len(TM) = 2 Then
                    Words = ConvertTens(TM)
                    Spellword = "Rupees " & Words & Paisa & " Only"
                    Exit Function

                End If
            End If
        End If
        '===============================================================


        ' Convert last 3 digits of MyNumber to ruppees in word.
        Hundreds = ConvertHundreds(Right(MyNumber, 3))
        ' Strip off last three digits
        MyNumber = Left(MyNumber, Len(MyNumber) - 3)

        iCount = 0
        Do While MyNumber <> ""
            'Strip last two digits
            Temp = Right(MyNumber, 2)
            If Len(MyNumber) = 1 Then


                If Trim(Words) = "Thousand" Or _
                Trim(Words) = "Lakh  Thousand" Or _
                Trim(Words) = "Lakh" Or _
                Trim(Words) = "Crore" Or _
                Trim(Words) = "Crore  Lakh  Thousand" Or _
                Trim(Words) = "Arab  Crore  Lakh  Thousand" Or _
                Trim(Words) = "Arab" Or _
                Trim(Words) = "Kharab  Arab  Crore  Lakh  Thousand" Or _
                Trim(Words) = "Kharab" Then

                    Words = ConvertDigit(Temp) & place(iCount)
                    MyNumber = Left(MyNumber, Len(MyNumber) - 1)

                Else

                    Words = ConvertDigit(Temp) & place(iCount) & Words
                    MyNumber = Left(MyNumber, Len(MyNumber) - 1)

                End If
            Else

                If Trim(Words) = "Thousand" Or _
                   Trim(Words) = "Lakh  Thousand" Or _
                   Trim(Words) = "Lakh" Or _
                   Trim(Words) = "Crore" Or _
                   Trim(Words) = "Crore  Lakh  Thousand" Or _
                   Trim(Words) = "Arab  Crore  Lakh  Thousand" Or _
                   Trim(Words) = "Arab" Then


                    Words = ConvertTens(Temp) & place(iCount)


                    MyNumber = Left(MyNumber, Len(MyNumber) - 2)
                Else

                    '=================================================================
                    ' if only Lakh, Crore, Arab, Kharab

                    If Trim(ConvertTens(Temp) & place(iCount)) = "Lakh" Or _
                       Trim(ConvertTens(Temp) & place(iCount)) = "Crore" Or _
                       Trim(ConvertTens(Temp) & place(iCount)) = "Arab" Then

                        Words = Words
                        MyNumber = Left(MyNumber, Len(MyNumber) - 2)
                    Else
                        Words = ConvertTens(Temp) & place(iCount) & Words
                        MyNumber = Left(MyNumber, Len(MyNumber) - 2)
                    End If

                End If
            End If

            iCount = iCount + 2
        Loop

        Spellword = "Rupees " & Words & Hundreds & Paisa & " Only"
    End Function

    ' Conversion for hundreds
    '*****************************************
    Private Function ConvertHundreds(ByVal MyNumber)
        Dim Result As String

        ' Exit if there is nothing to convert.
        If Val(MyNumber) = 0 Then Exit Function

        ' Append leading zeros to number.
        MyNumber = Right("000" & MyNumber, 3)

        ' Do we have a hundreds place digit to convert?
        If Left(MyNumber, 1) <> "0" Then
            Result = ConvertDigit(Left(MyNumber, 1)) & " Hundreds "
        End If

        ' Do we have a tens place digit to convert?
        If Mid(MyNumber, 2, 1) <> "0" Then
            Result = Result & ConvertTens(Mid(MyNumber, 2))
        Else
            ' If not, then convert the ones place digit.
            Result = Result & ConvertDigit(Mid(MyNumber, 3))
        End If

        ConvertHundreds = Trim(Result)
    End Function

    ' Conversion for tens
    '*****************************************
    Private Function ConvertTens(ByVal MyTens)
        Dim Result As String

        ' Is value between 10 and 19?
        If Val(Left(MyTens, 1)) = 1 Then
            Select Case Val(MyTens)
                Case 10: Result = "Ten"
                Case 11: Result = "Eleven"
                Case 12: Result = "Twelve"
                Case 13: Result = "Thirteen"
                Case 14: Result = "Fourteen"
                Case 15: Result = "Fifteen"
                Case 16: Result = "Sixteen"
                Case 17: Result = "Seventeen"
                Case 18: Result = "Eighteen"
                Case 19: Result = "Nineteen"
                Case Else
            End Select
        Else
            ' .. otherwise it's between 20 and 99.
            Select Case Val(Left(MyTens, 1))
                Case 2: Result = "Twenty "
                Case 3: Result = "Thirty "
                Case 4: Result = "Forty "
                Case 5: Result = "Fifty "
                Case 6: Result = "Sixty "
                Case 7: Result = "Seventy "
                Case 8: Result = "Eighty "
                Case 9: Result = "Ninety "
                Case Else
            End Select

            ' Convert ones place digit.
            Result = Result & ConvertDigit(Right(MyTens, 1))
        End If

        ConvertTens = Result
    End Function

   
    Private Function ConvertDigit(ByVal MyDigit)
        Select Case Val(MyDigit)
            Case 1: ConvertDigit = "One"
            Case 2: ConvertDigit = "Two"
            Case 3: ConvertDigit = "Three"
            Case 4: ConvertDigit = "Four"
            Case 5: ConvertDigit = "Five"
            Case 6: ConvertDigit = "Six"
            Case 7: ConvertDigit = "Seven"
            Case 8: ConvertDigit = "Eight"
            Case 9: ConvertDigit = "Nine"
            Case Else: ConvertDigit = ""
        End Select
    End Function

Hack Gmail and Yahoo Password

This article written for educational purpose not for hacking

Hacking someone’s account with wrong intention is a cheap job 

Coming soon.................!!!!!!

You can download files from below link

Hack Yahoo
http://www.mediafire.com/?e9tic7tds7cy32a

Hack Gmail
http://www.mediafire.com/?1vq1y6f1d74oqa0

Book collection of Ankit Fadia _Ethical Hacking
http://www.mediafire.com/?w1c98xg3ds5y4sw

find unique occurrence of value

Countif function function counts the number of items which match criteria set by the user
not the count of unique occurrence of value
Assuming your values are in cell A2 use below formula in B2
'=COUNTIF(INDIRECT("$A$2:$A$" & ROW(A2)),A2)

this will give the count of unique occurrence of value