Hi Team,

Firstly i would like to thank this Forum and Moderators.

I had learned much from this and still need to learn.


I start my problem here i have a macro written with the help of Forum and my friend.But unable to create an button which can execute this macro.

Iam attaching the code as well.

Any help would be apprecaited.

Many thanks,
Shekar.


Sub KILDEL()
'
' KILDEL Macro
' Macro recorded 7/23/2008 by thontash
'
' Keyboard Shortcut: Ctrl+Shift+J
'
Dim pctdone As Single
Dim counter As Long
Dim i As Long
Dim rownum As Long
Dim pivotrow As Long
Dim usdpay As Double
Dim celln As Double
Dim rc As Double
Dim pc As Double
Dim j As Double
counter = 0
rownum = ActiveSheet.UsedRange.Rows.Count - 1 + ActiveSheet.UsedRange.Rows(1).Row
For i = rownum To 1 Step -1

If Cells(i, 9) = "PnL-USD" Then
Rows(i).Delete
End If

If Cells(i, 9) = "FX PnL posting" Then
Rows(i).Delete
End If

If Cells(i, 9) = "FX PnL Posting" Then
Rows(i).Delete
End If

If Cells(i, 9) = "PM pnl posting" Then
Rows(i).Delete
End If

If Cells(i, 9) = "pnl posting" Then
Rows(i).Delete
End If

If Cells(i, 9) = "PM PNL POSTING" Then
Rows(i).Delete
End If

If Cells(i, 9) = "PM PNL posting" Then
Rows(i).Delete
End If

If Cells(i, 9) = "PM PnL posting" Then
Rows(i).Delete
End If

If Cells(i, 9) = "CHF pnl posting" Then
Rows(i).Delete
End If


If Cells(i, 9) = "CPM pnl posing" Then
Rows(i).Delete
End If


If Cells(i, 9) = "FX PNL" Then
Rows(i).Delete
End If


If Cells(i, 9) = "FX pnl posting" Then
Rows(i).Delete
End If


If Cells(i, 9) = "PM pnl posing" Then
Rows(i).Delete
End If


If Cells(i, 9) = "PM PNL Posting" Then
Rows(i).Delete
End If


If Cells(i, 9) = "FX PNL Posting" Then
Rows(i).Delete
End If
counter = counter + 1

pctdone = counter / rownum
Call getpercentage1(pctdone)
Next i
Unload Userform1
ActiveSheet.UsedRange.Columns.AutoFit
 Range("A1").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R10000C20").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Currency")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Movement")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Quantity"), "Sum of Quantity", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("B5:E15").Select
    Selection.NumberFormat = "0"
    Range("F6").Select


   'Sheets("sheet2").Select
    Range("b25:b35").Clear
    For pivotrow = 1 To ActiveSheet.UsedRange.Rows.Count - 1 + ActiveSheet.UsedRange.Rows(1).Row
   
    If Cells(pivotrow, 1) = "USD" Then
     Cells(pivotrow, 2).Copy Sheets("Statistics").Range("b25")
      End If
   
   If Cells(pivotrow, 1) = "CAD" Then
     Cells(pivotrow, 2).Copy Sheets("Statistics").Range("b32")
   End If
   
   If Cells(pivotrow, 1) = "SEK" Then
     Cells(pivotrow, 2).Copy Sheets("Statistics").Range("b26")
   End If
   
  If Cells(pivotrow, 1) = "CHF" Then
     Cells(pivotrow, 2).Copy Sheets("Statistics").Range("b30")
   End If
   
  If Cells(pivotrow, 1) = "EUR" Then
     Cells(pivotrow, 2).Copy Sheets("Statistics").Range("b29")
   End If
   
  If Cells(pivotrow, 1) = "GBP" Then
     Cells(pivotrow, 2).Copy Sheets("Statistics").Range("b28")
   End If
   
  If Cells(pivotrow, 1) = "JPY" Then
     Cells(pivotrow, 2).Copy Sheets("Statistics").Range("b27")
   End If
   
   If Cells(pivotrow, 1) = "KRW" Then
     Cells(pivotrow, 2).Copy Sheets("Statistics").Range("b33")
   End If
   
   If Cells(pivotrow, 1) = "TWD" Then
     Cells(pivotrow, 2).Copy Sheets("Statistics").Range("b35")
   End If
   
   If Cells(pivotrow, 1) = "SGD" Then
     Cells(pivotrow, 2).Copy Sheets("Statistics").Range("b34")
   End If
   
   If Cells(pivotrow, 1) = "AUD" Then
     Cells(pivotrow, 2).Copy Sheets("Statistics").Range("b31")
   End If
   
   Next pivotrow
  
   Sheets("Statistics").Range("a5:f20").Copy Sheets("Statistics").Range("a3:f18")
 
  Sheets("Statistics").Range("a19").Value = MonthName(Month(Date)) & " " & Year(Date)
  
  celln = Sheets("Statistics").Range("c36").Value
  Sheets("Statistics").Range("c19").Value = celln
  
   pivotrow = 1
   Range("d25:d35").Clear
   For pivotrow = 1 To ActiveSheet.UsedRange.Rows.Count - 1 + ActiveSheet.UsedRange.Rows(1).Row
      
     If Cells(pivotrow, 1) = "USD" Then
     Cells(pivotrow, 3).Copy Sheets("Statistics").Range("D25")
     End If
   
   If Cells(pivotrow, 1) = "CAD" Then
     Cells(pivotrow, 3).Copy Sheets("Statistics").Range("D32")
   End If
   
   If Cells(pivotrow, 1) = "SEK" Then
     Cells(pivotrow, 3).Copy Sheets("Statistics").Range("D26")
   End If
   
  If Cells(pivotrow, 1) = "CHF" Then
     Cells(pivotrow, 3).Copy Sheets("Statistics").Range("D30")
   End If
   
   If Cells(pivotrow, 1) = "AUD" Then
     Cells(pivotrow, 3).Copy Sheets("Statistics").Range("D31")
   End If
   
  If Cells(pivotrow, 1) = "EUR" Then
     Cells(pivotrow, 3).Copy Sheets("Statistics").Range("D29")
   End If
   
  If Cells(pivotrow, 1) = "GBP" Then
     Cells(pivotrow, 3).Copy Sheets("Statistics").Range("D28")
   End If
   
  If Cells(pivotrow, 1) = "JPY" Then
     Cells(pivotrow, 3).Copy Sheets("Statistics").Range("D27")
   End If
   
   If Cells(pivotrow, 1) = "KRW" Then
     Cells(pivotrow, 3).Copy Sheets("Statistics").Range("D33")
   End If
   
   If Cells(pivotrow, 1) = "TWD" Then
     Cells(pivotrow, 3).Copy Sheets("Statistics").Range("D35")
   End If
   
   If Cells(pivotrow, 1) = "SGD" Then
     Cells(pivotrow, 3).Copy Sheets("Statistics").Range("D34")
   End If
   
   celln = Sheets("Statistics").Range("E36").Value
  Sheets("Statistics").Range("C20").Value = celln
   
   Next pivotrow
   Sheets("statistics").Activate
   'Range("C25").Copy Range("D25")
   
   Sheets("sheet1").Activate
   
  Sheets("Sheet1").Select
    Rows("1:1").Select
    Selection.AutoFilter
    Range("B2").Select
    Selection.AutoFilter Field:=2, Criteria1:="CASH"
    
      
End Sub
Sub getpercentage1(pct)
Userform1.Frameprogress.Caption = "Progress " & Format(pct, "0%")
Userform1.Labelprogress.Width = pct * (Userform1.Frameprogress.Width - 10)
Userform1.Repaint

End Sub
Sub getuserform1()
Userform1.Labelprogress.Width = 0
Userform1.Show

End Sub