What is the syntax to clear a filter but leave drop-down icons?

Sheets("<sheet name>").ShowAllData
'examples
Sheets("RevenueSummary").ShowAllData
ActiveSheet.ShowAllData

For another version of clear filter that does remove the filter drop-down icons, click here.

Advertisements

What is the syntax to clear an autofilter?

Sheets("<sheet name>").Range("<chart cell>").AutoFilter
'where chart cell is the right-most, top-most cell containing filtered data
'clears the filter dropdown icons as well
'example
Sheets("RevenueSummary").Range("A1").AutoFilter

For another version of clear filter that doesn’t remove the filter drop-down icons, click here.

 

What is the syntax to write to another workbook?

'to retrieve data
Workbooks("<workbook file name>").Worksheets("<sheet name>").Range("<your range>").Value
Workbooks("<workbook file name>").Worksheets("<sheet name>").Cells(<row number>, <column number>).Value

'to set data
Workbooks("<workbook file name>").Workbooks("Worksheets("<sheet name>").Range("<your range>") = <your value/data>
Workbooks("<workbook file name>").Worksheets("<sheet name>").Cells(<row number>, <column number>).Value = <your value/data>
'examples
Workbooks("example.xlsm").Worksheets("Example").Range("A1:A4") = "hello"
Workbooks("book3.xlsx").Worksheets("Example").Range("D1") = "world"
Workbooks("book3.xlsx").Worksheets("Example").Cells(2, 4).Value = "goodbye"

What is the syntax to use/set data in another sheet?

'to retrieve data
Worksheets("<sheet name>").Range("<your range>").Value
Worksheets("<sheet name>").Cells(<row number>, <column number>).Value

'to set data
Worksheets("<sheet name>").Range("<your range>") = <your value/data>
Worksheets("<sheet name>").Cells(<row number>, <column number>).Value = <your value/data>
'examples
Worksheets("Example").Range("A1:A4") = "hello"
Worksheets("Example").Range("D1") = "world"
Worksheets("Example").Cells(2, 4).Value = "goodbye"

Capture.PNG