Just random mumbling: French MBA College VBA Assignment done

Showing posts with label French MBA College VBA Assignment done. Show all posts

Thursday, February 16, 2017

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