Tutorialized Stuffs

Public Function GetFilteredData()
    Dim rawWs As Worksheet            'RAW DATA WORKSHEET
    Dim tarWs As Worksheet            'TARGET WORKSHEET
    
    'Replace this with your actual Worksheets
    Set rawWs = Sheets("Raw Data")    
    Set tarWs = Sheets("Filtered Data Visualizations")
    
    Application.ScreenUpdating = False

    'Clear old contents of the Target Worksheet
    tarWs.Range("A2:N" & Rows.Count).ClearContents
    
    '****************************************************
    ' Select Raw Data Sheet and
    ' Copy only the visible rows if filter is applied
    '
    rawWs.Select
    Range("A2", Cells(ActiveSheet.UsedRange.Rows.Count, Range("N2").Column)).SpecialCells(xlCellTypeVisible).Copy
    
    '****************************************************
    'Select the Target worksheet and
    'Paste the copied data
    '
    tarWs.Select
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    Range("A2").Select
    Application.ScreenUpdating = True
End Function

Dim FilteredRows as Variant

Public Function GetFilteredRows(Optional ByVal RowPrefixed As Boolean)

    Dim Rng As Range, rngF As Range, rngVal As Range      'Ranges
    Dim val As Variant       'Range Value
    Dim i As Integer         'Counter
    Dim lRow as long         'Last Row

    Application.ScreenUpdating = False

    Sheets("Raw Data").Select
    lRow = WorksheetFunction.CountA(Range("A:A"))

    'Set the range of all visible cells of the filtered data
    Set rngF = Range("A2", Cells(ActiveSheet.UsedRange.Rows.Count, _
    Range("A2").Column)).SpecialCells(xlCellTypeVisible)
    
    For Each Rng In Range("$A2:$A$" & lRow)
        If Not Intersect(Rng, rngF) Is Nothing Then
            If rngVal Is Nothing Then
                Set rngVal = Rng
            Else
                Set rngVal = Union(rngVal, Rng)
            End If
            If rngVal.Cells.Count = lRow Then Exit For
        End If
    Next Rng
    
    'Resize array variable
    ReDim FilteredRows(0 To Application.CountA(rngVal)) As Variant
    
    For Each val In rngVal
        If RowPrefixed = True Then
            FilteredRows(i) = val.Address
        Else
            FilteredRows(i) = Split(val.Address, "$")(2)
        End If

        Debug.Print val.Address & " - " & Split(val.Address, "$")(2)
        i = i + 1
    Next val
    
    Debug.Print rngVal.Address
    
    Applicaiton.ScreenUpdating = True
End Function

Sub SetFilter()
    Call GetFilteredRows(True)
End Sub

Application.DisplayAlerts = False

'DO SOME TASKS HERE

Application.DisplayAlerts = True

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target       
        'SET THE TARGET COLUMN TO COLUMNS 7 AND 8
        If Target.Column >= 7 And Target.Column <= 8 Then
            'SELECT CASE OF WHAT ROW WILL BE SELECTED
            Select Case Target.Row
                Case 19 'SICK LEAVE
                    CheckSelected 19, 7
                Case 21 'VACATION LEAVE
                    CheckSelected 21, 7
                Case 23 'PERSONAL
                    CheckSelected 23, 7
                Case 25 'EMERGENCY
                    CheckSelected 25, 7
                Case 27 'APPOINTMENT
                    CheckSelected 27, 7
                Case 29 'OTHERS
                    CheckSelected 29, 7
                Case Default
            End Select
        End If
    End With
End Sub



'CHECK IF THE REASON IS CURRENTLY SELECTED
Sub CheckSelected(Row As Integer, Col As Integer)
    With Cells(Row, Col)
        If .Value = "+" Then
            .Value = ""
            ResetSelectedColor Row, Col
        Else
            .Value = "+"
            ApplySelectedColor Row, Col
        End If
    End With
End Sub



'CHANGE THE BACKGROUND OF THE SELECTION
Sub ApplySelectedColor(Row As Integer, Col As Integer)
    With Cells(Row, Col).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
End Sub


'REMOVE THE BACKGROUND OF THE SELECTION
Sub ResetSelectedColor(Row As Integer, Col As Integer)
    With Cells(Row, Col).Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

• Write your formulas like this =C1-(C1*Discount). This is more intuitive when you come to review the spreadsheet later, or for anyone else reviewing it.
• Update the value globally by editing it once in the Name Manager. This will then automatically update the formulas.
• Reduce clutter on your spreadsheet. Often a helper cell is set up with this key figure in to achieve these benefits, but if you've got many of these key figures your spreadsheets can become crowded.

If you're working on a project or having a numerous reports in excel to be sent out to your boss or clients. And what you usually do is save the workbook, compose a new email, copy the contents or attach it on your email client. That's a time consuming task!

Function SendEmail(ByVal Username As String, _
                   ByVal Password As String, _
                   ByVal ToAddress As String, _
                   ByVal Subject As String, _
                   ByVal HTMLMessage As String, _
                   ByVal SMTPServer As String, _
                   Optional Attachment As Variant = Empty) As Boolean

• Username - is the email address of the sender.
• Password - is the password of the sender.
• ToAddress - is the recipient of email to which the email be sent. Multiple email addresses can be separated with semi-colons.
• Subject - is the subject of the email.
• HTMLMessage - may contain both plain text and html message. 
• SMTPServer - is the name of the outgoing email server. If you're connected within a company's intranet you can use your company's outgoing email server. In this tutorial we'll be using gmail's smtp server.
• Attachment - is the file name that will be attached to the message. If you're going to send the workbook that you're working with as an attachment, you can just put ThisWorkbook.FullName.

Sub Send()
    Dim Ws As Worksheet
    Dim Attachment As String
    
    Set Ws = ActiveSheet
    
    With Ws

        If Trim(.Range("ATTACHMENT")) = "" Then
            ThisWorkbook.Save
            ThisWorkbook.ChangeFileAccess xlReadOnly
            Attachment = ThisWorkbook.FullName
            ThisWorkbook.ChangeFileAccess xlReadWrite
        Else
            Attachment = .Range("ATTACHMENT")
        End If

        'CHECK WHETHER THE FUNCTION RETURNS TRUE OR FALSE
        If SendEmail(.Range("SENDER"), .Range("PASS"), .Range("RECIPIENT"), _
                  .Range("SUBJECT"), .Range("MESSAGE"), .Range("SMTP"), Attachment) = True Then
            MsgBox "Email was successfully sent to " & .Range("RECIPIENT") & ".", vbInformation, "Sending Successful"
        Else
            MsgBox "A problem has occurred while trying to send email.", vbCritical, "Sending Failed"
        End If

    End With

End Sub

Function SendEmail(ByVal Username As String, _
                   ByVal Password As String, _
                   ByVal ToAddress As String, _
                   ByVal Subject As String, _
                   ByVal HTMLMessage As String, _
                   ByVal SMTPServer As String, _
                   Optional Attachment As Variant = Empty) As Boolean

    Dim Mail As New Message
    Dim Cfg As Configuration
    
    'CHECK FOR EMPTY AND INVALID PARAMETER VALUES
    If Trim(Username) = "" Or _
        InStr(1, Trim(Username), "@") = 0 Then
        SendEmail = False
        Exit Function
    End If
    
    If Trim(Password) = "" Then
        SendEmail = False
        Exit Function
    End If
    
    If Trim(Subject) = "" Then
        SendEmail = False
        Exit Function
    End If
    
    If Trim(SMTPServer) = "" Then
        SendEmail = False
        Exit Function
    End If
    
    
    On Error Resume Next
    Set Cfg = Mail.Configuration
    
    'SETUP MAIL CONFIGURATION FIELDS
    Cfg(cdoSendUsingMethod) = cdoSendUsingPort
    Cfg(cdoSMTPServer) = SMTPServer
    Cfg(cdoSMTPServerPort) = 25
    Cfg(cdoSMTPAuthenticate) = cdoBasic
    Cfg(cdoSMTPUseSSL) = True
    Cfg(cdoSendUserName) = Username
    Cfg(cdoSendPassword) = Password
    Cfg.Fields.Update
    
    If err.Number <> 0 Then
        SendEmail = False
        Exit Function
    End If
    err.Clear
    
    On Error GoTo 0
    With Mail
        .From = Username
        .To = ToAddress
        .Subject = Subject
        .HTMLBody = HTMLMessage

        If Attachment <> "" Then
            .AddAttachment Attachment
        End If
        
        On Error Resume Next
        err.Clear

        'SEND EMAIL
        .Send
    End With
    If err.Number = 0 Then
        SendEmail = True
    Else
        SendEmail = False
        Exit Function
    End If
    
End Function

• Disable all macros without notification - Select this option when you don't want to run all macros from unknown sources and untrusted publisher's location.
• Disable all macros with notification (Default) - Select this option when you want to manage and check its trustworthiness before running the macro. 

• Disable all macros except digitally signed macros - Select this option if you want to automatically run trustworthy digitally signed macros without raising notification.
• Enable all macros (not recommend potentially dangerous code can run) - Selecting this option is totally at your own risk, malicious codes can freely run and may cause damage to your system.

•  IFERROR('Formula to Evaluate', value-if-error) - Returns the specified value "No Match Found" if the formula evaluated returns an error value.
•  IF(logical_test, [value_if_true], [value_if_false]) - Logic test is a comparison between two values (G3=""), test's if the value of G3 is empty. Followed by two arguments which are the value_if_true and value_if_false. If the value of G3 is equal to blank then do nothing or set the Cell's value to blank. While, if the value if G3 is not equal to blank then evaluate the next function.
• VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) - Looks up  the value of G3 in the table array Sheet1!B:C and return the value of the exact match from the column 2.

=IF(C2<>"",VLOOKUP(B2,Price_List!B1:C7,2,FALSE) *C2,VLOOKUP(B2,Price_List!B1:C7,2,FALSE))

If the quantity "C2" is blank then it will just lookup the unit price from the Price_List Sheet. And if the quantity is entered it will calculate by multiplying the unit price with the quantity value.