Aim clean filter

Aim clean filter


Option Explicit

Sub update_data()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = "Processing... Please Be Patient."

Sheets("Sheet2").Visible = True
Sheets("Result").Visible = True
Sheets("ResultMTD").Visible = True
Sheets("MTD_Month").Visible = True

Call FORMAT_DATA
Call mtd_toresult
Call mtd_tomonth_result

Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayAlerts = True
Sheets("Macro").Select
MsgBox " ! Done ! "
Sheets("Sheet2").Visible = False
Sheets("Result").Visible = False
Sheets("ResultMTD").Visible = False
Sheets("MTD_Month").Visible = False

End Sub


Sub FORMAT_DATA()
Dim i As Integer
Dim k As Integer
Dim l As Integer
Dim a As Integer

Sheets("Sheet2").Select
Cells.Clear
Sheets("Result").Select
Cells.Clear
Sheets("ResultMTD").Select
Cells.Clear
Sheets("MTD_Month").Select
Cells.Clear


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = "Processing... Please Be Patient."
Sheets("RawData").Select
Sheets("RawData").Range("a1").CurrentRegion.Select
Selection.Copy Sheets("Sheet2").Range("a1")
Sheets("Sheet2").Select
a = Range("A1").CurrentRegion.Columns.Count
k = 1
l = 1
Columns(a).Select
Selection.Delete shift:=xlToLeft
For i = 3 To a
Sheets("sheet2").Select
    Rows("2:2").Select
    Selection.AutoFilter
    ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:=">=0.04", Operator:=xlOr, Criteria2:="<=-0.04"
ActiveSheet.Columns("a:C").Copy Sheets("Result").Cells(k, l)
'l = Sheets(3).Range("a" & l).CurrentRegion.Columns.Count
l = Sheets("Result").UsedRange.Columns.Count
l = l + 2
Sheets("Sheet2").Select
Selection.AutoFilter
Sheets("Sheet2").Columns("C").Select
Selection.Delete shift:=xlToLeft
Next
Sheets("Result").Activate
ActiveSheet.Columns("A:dw").AutoFit
ActiveSheet.UsedRange.Font.Size = 8

Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayAlerts = True
Sheets("Macro").Activate

Sheets("Sheet2").Visible = False
Sheets("Result").Visible = False
Sheets("ResultMTD").Visible = False
Sheets("MTD_Month").Visible = False

End Sub
Sub Daterange()

Application.DisplayAlerts = False
Dim fstr, fstr1 As String
Dim ak, b, a2, a12, J, UI As Integer
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Result").Visible = True
Sheets("ResultMTD").Visible = True
Sheets("MTD_Month").Visible = True

Sheets("Macro").Select
    '-------------below code is for copy the data for given date range to sheet1------------------
Range("a10:c110 ").ClearContents
 Sheets("Sheet1").Select
 ActiveSheet.UsedRange.ClearContents
 Sheets("Rough").Select
 ActiveSheet.UsedRange.ClearContents
 Sheets("Macro").Select
  fstr = InputBox("Enter From date.. You wanted to See !")
fstr1 = InputBox("Enter TO date.. You wanted to See !")
If fstr <> "" Then
Sheets("Result").Select
   Cells.Find(What:=fstr, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ak = ActiveCell.Column
If fstr1 <> "" Then
Sheets("Result").Select
   Cells.Find(What:=fstr1, After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    b = ActiveCell.Column
   ak = ak - 2
Range(Cells(1, ak), Cells(1, b)).Select
        Selection.EntireColumn.Copy
    Sheets("Sheet1").Select
    Range("a1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWorkbook.Save
    '-------------above code is for copy the data for given date range to sheet1------------------

'-------------below code is deleteing unnecessary columns in sheet1 ------------------
Sheets("Sheet1").Select
a2 = Sheets("sheet1").UsedRange.Columns.Count
Cells(1, a2 + 1) = "Santhosh-Chek"
For J = 5 To a2
UI = Cells(2, J).CurrentRegion.Rows.Count
If UI < 3 Then
Cells(2, J).CurrentRegion.Select
Selection.EntireColumn.Delete
End If
Next
a12 = Sheets("Sheet1").UsedRange.Columns.Count
For J = 4 To a12
If Cells(3, J) = "" Then
If Cells(1, J) <> "Santhosh-Chek" Then
Cells(3, J).Select
Selection.EntireColumn.Delete
J = J - 1
End If
End If
If Cells(1, J) = "Santhosh-Chek" Then
Cells(1, J).Select
Selection.EntireColumn.Delete
Exit For
End If
Next
End If
End If
'-------------above code is deleteing unnecessary columns in sheet1 ------------------

Dim PQ, K1, k, AAA As Integer

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Rough"
Sheets("Sheet1").Select
ActiveSheet.UsedRange.Select
Selection.Copy
Sheets("Rough").Select
Range("a1").PasteSpecial Paste:=xlPasteAll
AAA = Sheets("Rawdata").Range("a1").CurrentRegion.Rows.Count
Sheets("rawdata").Select
Range("a1", "b" & AAA).Copy Sheets("sheet3").Range("a1")
Sheets("Rough").Select
PQ = Range("a2").CurrentRegion.Columns.Count
PQ = PQ / 3
PQ = PQ + 2
Sheets("sheet3").Select
K1 = 3
For k = 3 To PQ
Sheets("Sheet3").Select
Cells(2, k) = Sheets("Sheet1").Cells(2, K1)
Cells(3, k).Select
ActiveCell.Formula = "=IFERROR(VLOOKUP($A3,Rough!A:C,3,0),"""")"
ActiveCell.AutoFill Range(Cells(3, k), Cells(AAA, k))
K1 = K1 + 3
ActiveSheet.UsedRange.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Sheets("Rough").Select
Columns("A:C").Select
Selection.Delete shift:=xlToLeft
Next
Sheets("Sheet3").Select
Columns.AutoFit
Range("d10").Select
Sheets("Rough").Delete
Sheets("Sheet2").Visible = False
Sheets("Sheet1").Visible = False
Sheets("Result").Visible = False
Sheets("ResultMTD").Visible = False
Sheets("MTD_Month").Visible = False
Application.DisplayAlerts = True
End Sub

Sub mtd_toresult()
Sheets("Sheet2").Visible = True
'Sheets("Result").Visible = True
Sheets("ResultMTD").Visible = True
Sheets("MTD_Month").Visible = True

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = "Processing... Please Be Patient."


Sheets("Sheet2").Select
Cells.Clear
Sheets("RawData").Select
lastd = Sheets("RawData").Range("a1").End(xlDown).Row
lastd = lastd + 2
Range("a" & lastd).CurrentRegion.Select
Selection.Copy Sheets("Sheet2").Range("a1")
Sheets("Sheet2").Select
a = Range("A1").CurrentRegion.Columns.Count
k = 1
l = 1
'Columns(a).Select
'Selection.Delete shift:=xlToLeft
For i = 3 To a
Sheets("sheet2").Select
    Rows("2:2").Select
    Selection.AutoFilter
    ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:=">=0.05", Operator:=xlOr, Criteria2:="<=-0.05"
ActiveSheet.Columns("a:C").Copy Sheets("ResultMTD").Cells(k, l)
'l = Sheets(3).Range("a" & l).CurrentRegion.Columns.Count
l = Sheets("ResultMTD").UsedRange.Columns.Count
l = l + 2
Sheets("Sheet2").Select
Selection.AutoFilter
Sheets("Sheet2").Columns("C").Select
Selection.Delete shift:=xlToLeft
Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Sheets("Sheet2").Visible = False
Sheets("Result").Visible = False
Sheets("ResultMTD").Visible = False
Sheets("MTD_Month").Visible = False

End Sub

Sub Monthly_todate()

Sheets("Sheet2").Visible = True
Sheets("Result").Visible = True
Sheets("ResultMTD").Visible = True
Sheets("MTD_Month").Visible = True

On Error GoTo sant
Sheets("Macro").Select
Range("f10: i110 ").ClearContents
fstr = InputBox("Enter the date.. You wanted to See !")
'fstr = Range("J11").Value
If fstr <> "" Then
Sheets("MTD_Month").Select
   Cells.Find(What:=fstr, After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.CurrentRegion.Select
    Selection.Copy
    Sheets("Macro").Select
    Range("f10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWorkbook.Save
   Range("f9") = fstr
End If

Range("d10").Select

sant:
Sheets("Sheet2").Visible = False
Sheets("Result").Visible = False
Sheets("ResultMTD").Visible = False
Sheets("MTD_Month").Visible = False

End Sub


Sub DEFAULT_MTDDATE()

Sheets("Sheet2").Visible = True
Sheets("Result").Visible = True
Sheets("ResultMTD").Visible = True
Sheets("MTD_Month").Visible = True
On Error GoTo sant
Sheets("Macro").Select
Range("f10: i110 ").ClearContents
Sheets("ResultMTD").Select
LCOLUMN = ActiveSheet.UsedRange.Columns.Count
fstr = Cells(2, LCOLUMN).Value
'lColumn = ws.Cells(2, Columns.Count).End(xlToLeft).Column
'a = Range("A").CurrentRegion.Columns.Count
Cells(1, LCOLUMN).CurrentRegion.Select
     Selection.Copy
    Sheets("Macro").Select
    Range("f10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWorkbook.Save
    Range("f9") = fstr

Columns.AutoFit


Range("d10").Select

sant:
Sheets("Sheet2").Visible = False
Sheets("Result").Visible = False
Sheets("ResultMTD").Visible = False
Sheets("MTD_Month").Visible = False



End Sub


Sub mtd_tomonth_result()
Sheets("Sheet2").Visible = True
Sheets("Result").Visible = True
Sheets("ResultMTD").Visible = True
Sheets("MTD_Month").Visible = True

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = "Processing... Please Be Patient."
Sheets("Sheet2").Select
Cells.Clear
Sheets("RawData").Select
lastd = Sheets("RawData").Range("a1").End(xlDown).Row
lastd = lastd + 2
Range("a" & lastd).CurrentRegion.Select
Selection.Copy Sheets("Sheet2").Range("a1")
Sheets("Sheet2").Select
a = Range("A1").CurrentRegion.Columns.Count
k = 1
l = 1
'Columns(a).Select
'Selection.Delete shift:=xlToLeft
For i = 3 To a
Sheets("sheet2").Select
    Rows("2:2").Select
    Selection.AutoFilter
    ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:=">=0.20", Operator:=xlOr, Criteria2:="<=-0.20"
ActiveSheet.Columns("a:C").Copy Sheets("MTD_Month").Cells(k, l)
'l = Sheets(3).Range("a" & l).CurrentRegion.Columns.Count
l = Sheets("MTD_Month").UsedRange.Columns.Count
l = l + 2
Sheets("Sheet2").Select
Selection.AutoFilter
Sheets("Sheet2").Columns("C").Select
Selection.Delete shift:=xlToLeft
Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Sheets("Macro").Select
Sheets("Sheet2").Visible = False
Sheets("Result").Visible = False
Sheets("ResultMTD").Visible = False
Sheets("MTD_Month").Visible = False

End Sub
Sub DLARED()

Sheets("Macro").Select
Range("a9:i110 ").ClearContents
End Sub

Sub ASOFDATE()
Sheets("Sheet2").Visible = True
Sheets("Result").Visible = True
Sheets("ResultMTD").Visible = True
Sheets("MTD_Month").Visible = True

Sheets("Macro").Select
Range("a10:c110 ").ClearContents
fstr = InputBox("Enter the date.. You wanted to See !")
'fstr = Range("J11").Value
If fstr <> "" Then
Sheets("Result").Select
   Cells.Find(What:=fstr, After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.CurrentRegion.Select
    Selection.Copy
    Sheets("Macro").Select
    Range("a10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWorkbook.Save
    Range("a9") = fstr
    Range("c11") = fstr
End If
Columns.AutoFit
Range("d10").Select

Sheets("Sheet2").Visible = False
Sheets("Result").Visible = False
Sheets("ResultMTD").Visible = False
Sheets("MTD_Month").Visible = False
End Sub

Comments

Post a Comment

Popular posts from this blog

number format

  Selection.NumberFormat = "0.00" --------------- =OFFSET(G1094,-1,0)/OFFSET(G1094,-12,0)-1 ---------------- Sub test() ' to select all sheets in the summary workbook Dim i As Long Sheet1.Select For i = 2 To ThisWorkbook.Sheets.Count     If Sheets(i).Name <> "Summary" Then Sheets(i).Select Replace:=False Next i End Sub  --------------- Sub LastRowInOneColumn()  'Find the last used row in a Column: column F in this     Dim LastRow As Long     With ActiveSheet         LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row     End With     MsgBox LastRow End Sub --------------------------------- Sub tests() ' to place vlookup in each and every sheet other than summary Dim i As Long Sheet1.Select For i = 2 To ThisWorkbook.Sheets.Count     If Sheets(i).Name <> "Summary" Then Sheets(i).Select Replace:=False     a = Range("f7").End(xlDown).Row ...
Read more

send Mail by outlook from excel sheet

Sub outlookmailxlsheet()     Dim rng As Range     Dim OutApp As Object     Dim OutMail As Object       Set rng = Nothing     Set rng = Sheets("YourSheet").UsedRange     Set OutApp = CreateObject("Outlook.Application")     Set OutMail = OutApp.CreateItem(0)     On Error Resume Next     With OutMail         .To = "XX@gmail.com"         .CC = ""         .BCC = ""         .Subject = "This is the Subject line"         .HTMLBody = RangetoHTML(rng)         .Send   'or use .Display     End With     With Application         .EnableEvents = True         .ScreenUpdating = True     End With     Set OutMail = Nothing     Set OutApp = Nothing End Sub Fun...
Read more

Find and copy the data

Sub CLE() 'ONEYEAR DATA Sheets("Sheet2").Select 'Range("j10: L110 ").ClearContents 'FSTR = InputBox("Enter the date.. You wanted to See !") lastrow = Sheets("sheet2").UsedRange.Rows.Count On Error GoTo sant For I = 6 To lastrow If Range("C" & I) <> "" Then fstr = Sheets("sheet2").Range("c" & I).Value Sheets("Sheet1").Select             Cells.Find(What:=fstr, After:=ActiveCell, _             LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _             SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate             'ActiveCell.CurrentRegion.Select A = ActiveCell.Row 'MsgBox A + 2 A = A + 2 Range("f" & A).Select     Selection.Copy     Sheets("Sheet2").Select     Range("e" & I).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, ...
Read more