French MBA College VBA Assignment done,vba teacher sourav,kolkata 09748184075

Sub advancedfilter()
Sheets("Extraction").Select
Cells.Clear
Range("A1").Select
ActiveCell.Value = "CustomerID"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "DateandTime"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Date"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Time"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "ProductID"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "ProductName"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Unit Price"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Quantity"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Total Price"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Discount"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Final Price"
ActiveCell.Offset(0, 1).Select

'January Sheet data copy


Sheets("January").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("A")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("January").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("B")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("January").Select
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("E")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("January").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("H")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False

'February Month Data Copy

Sheets("February").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("A")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("February").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("B")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("February").Select
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("E")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("February").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("H")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


'March Month Data Copy

Sheets("March").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("A")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("March").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("B")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("March").Select
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("E")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("March").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("H")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False

Sheets("Extraction").Select
Range("C2").Select

    ActiveCell.Select
    ActiveCell.FormulaR1C1 = _
        "=TEXT(RC[-1],""d"")&""/""&TEXT(RC[-1],""m"")&""/""&TEXT(RC[-1],""yy"")"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=TIME(HOUR(RC[-2]),MINUTE(RC[-2]),SECOND((RC[-2])))"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
   ActiveCell.Range("A1:A12000").Select

Sheets("Extraction").Select
Range("F2").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Products!R3C1:R18C3,2,FALSE)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    
    
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Products!R3C1:R18C3,3,FALSE)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select


Range("I2").Select

    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    
    Range("J2").Select
      ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(Extraction!RC[-1],Discounts,2,TRUE)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    Selection.NumberFormat = "0.00%"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0%"
    
    
    Range("K2").Select
    
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=RC[-2]-(RC[-2]*RC[-1])"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    Selection.NumberFormat = "0.00"

Range("A:Z").EntireColumn.AutoFit


End Sub