Autofilter: To filter with macro

Below is very basic macro to define filter on atable which has two columns. Name(Comuln A) and Dept(Column B). Function will filter all employees with dept = "Technology"

With ThisWorkbook.Worksheets(1)

'Step 1: Remove all filters from the sheet by setting AutoFilterMode property to false.

.AutoFilterMode = False

'Step 2: Optional Step. Select the table on which you want to apply filter.

R = .Range("A100").End(xlUp).Row
.Range("A1:D" & R).Select

'Step 3: Select the Headers

With .Range("A1:D1")

'Step 4: Define filter with .Autofilter

    .AutoFilter field:=2, Criteria1:="Technology"

End With
End With

Please note in step 4,
                     field : sequence of column in table.
                     criteria1: defined which value you want to filter. There can be multiple criterias.

Further example:
Lets say Column C in table contains employee grade and you want to get employees with grades >2 and <5:

 .AutoFilter field:=3, Criteria1:=">2", Criteria2:="<5"

Copy paste with macros

With ThisWorkbook

  'select the last row in a sheet
    r = .Worksheets(1).Range("A100").End(xlUp).Row

    'copy the rows from cell A1 to last diagnally filled cell
    .Worksheets(1).Range("A" & 1, "E" & r).Copy

  'activate the target worksheet and paste values and formats there        
    .Worksheets("Sheet2").Activate
     ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
     ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
     
End With

Self Note on Range as variable

Sub CleaningData()

Dim targetName As Range

    For Each targetName In Range("A2:A147")
    
    Sheets("Customers").Range("D3").Value = targetName.Value
    
        If Range("D4") = 0 Then
        
            Range("j1").Formula = "=counta(F:F)"
            
            Dim r As Integer
            
            r = Range("J1").Value + 1
            
            Range("F" & r).Value = targetName.Value
            
            Range("G" & r).Value = targetName.Address
        
        End If
    
    Next targetName

End Sub

How to Reach Last Blank Row in a Column

Following code brings you the row number of last blank row in a table, even if there are blank values in table. This is because it travels from any below line to upward, reaches the last row with end(xlUp) and then addition of 1 provides you with last bank row. Here you may fill new data.

With wb.Worksheets("Sheet1")
        r = .Range("A3000").End(xlUp).Row + 1
        .Range("A" & r).Select
        .Range("A" & r) = .Range("A" & r - 1).Value + 1
End With

Opening a Workbook, Clearing Contents, Adding New Content, Saving&Closing

Setting full address of file:

Dim str, filePath As String
str = ThisWorkbook.Path & "\"
filePath = str & "destination" & ".xlsx"

Opening workbook:

Dim wb As Workbook

Set wb = Workbooks.Open(filePath)

Clearing all contents,formats and comments for a sheet, say Sheet1:

wb.Worksheets("Sheet1").Cells.ClearContents

wb.Worksheets("Sheet1").Cells.ClearFormats

wb.Worksheets("Sheet1").Cells.ClearComments

Putting some data:

     With wb.Worksheets("Sheet1")

        Dim i As Integer

        For i = 1 To 100

            .Range("A" & i).Value = i

        Next i

     End With       

Save and Close:

        .Save

        .Close

     

Working with Multiple Open Workbooks

This post is only about scenario when workbooks, to work with, are open. If any workbook remains close then "Subscript out of range" error will be thrown.

Consider two workbooks:

destination.xlsm : where data will be pasted. Here data is values in two cells - for simplicity.
source.xlsm: cell values will be copied from sheet1 of this workbook.

Code:

Declaration and setting of two workbook variables:

Dim wbRead As Workbook
Set wbRead = Workbooks("source.xlsx")
Dim wbWrite As Workbook
Set wbWrite = Workbooks("destination.xlsx")


Copy paste values from cells A1 and B1 of source to A1 and A2 of destination.

wbWrite.Worksheets(1).Range("A1") = wbRead.Worksheets(1).Range("A1")
wbWrite.Worksheets(1).Range("A2") = wbRead.Worksheets(1).Range("B1")

Save and Close destination workbook:

wbWrite.Save

wbWrite.Close

Accessing Workbook via VBA Code

When accessing current workbook:

use ThisWorkBook method

Debug.Print ActiveWorkbook.FullName


When accessing Any open workbook:

use Workbooks("name")

Debug.Print Workbooks("test.xlsx").FullName

Please note: above example will throw script out of range error if workbook is not opened or you mention full path of workbook.

Avoid using ActiveWorkbook and Workbooks(index)