Tables in Mac Excel with VBA

We can convert a normal range with data into a Table in Excel to give us many benefits, select a cel in your data and use Insert>Table in the Ribbon. You will see the Create Table dialog like this :
tablecreate
There are many benefits when you use a Table instead of a normal range in your worksheet. After you defined the Table in Excel you will have access to a Ribbon tab named Table with for example:

  • Table Styles gallery for different color schemes
  • Add a total row with one click
  • Option to remove duplicates
  • And many other options

A few other benefits if your data is in a Table in Excel are:

No problem if there is an empty row or column when you filter your Table. It will automatic turn on AutoFilter in the Table so you can use the AutoFilter dropdown to sort or Filter. When you scroll past the column headers, the headers at the top of screen will automatically switch to display the header names. If you add a record below the last row in the Table or add a column to the right or left then the Table will automatically expend to include the new row or column. Charts or Pivot Tables based on a Table will automatically update if your Table size changes. If you enter a formula in the first cell off the Table row it will automatically fill down the whole field. If you edit one formula in a field all formulas in that field will update automatically.

Download Example file

Download Example file (30-12-2020) with the VBA examples below :

  • Copy visible data from the Table to a new worksheet
  • Copy visible data from the Table to a new workbook
  • Filter in Place with VBA code
  • Filter and copy the visible data to a new worksheet
  • Filter and copy the visible data to a new workbook
  • Copy the data for every unique value to own worksheet
  • Copy the data for every unique value to own workbook

Note: Application.CutCopyMode = False will not clear the clipboard like in Win Excel.
Note
: The code examples will copy as Values and Formats on this moment.
23/03/2024
Web design by Will Woodgate