When you want to quickly find the methods and properties that accomplish a simple task, record a macro and the use the output of the Macro Recorder as a quick help reference.
Whenever you have a list that you need to sort or filter, consider turning it into a table. Use the worksheets ListObjects collection to create or access a table.
Use ThisWorkbook.Path as a basis to help you open files that will always be close to the macro workbook.
The recorder is great for determining the connection string for an external reference, such as a database. Many of the parameters in a recorded connection string are default values, so you can omit them from your macro if you choose.
Creating formulas that refer to cells in a table results in structured references. If the formula is inside the table, you can often simplify the default formula to get just what is essential for your needs.
When you create a PivotTable report, you first create a memory cache to store the data. You can create many PivotTable reports that share the same memory cache. To access the memory cache, use the PivotCache property of a PivotTable object.
To modify the style for a PivotTable report or for a regular table, you first duplicate an existing style. Use the TableStyleElements collection to find and select the part you want to change.