Create and Copy AutoCorrect List Items

Create and Copy AutoCorrect List Items

To save time, create AutoCorrect entries for words, phrases, and even symbols that you type frequently. Then, type a short code, and Excel automatically changes it to the full text. See how to create an entry, then print a list of all your entries, and copy them to a different computer, using the AutoCorrect macros below.

Create Excel AutoCorrect Entries

The programs in Microsoft Office share a common list of AutoCorrect entries. Any entries that you create or change in Excel, will also be available in Word, Outlook, and PowerPoint.

To see the AutoCorrect list:

  • On the Excel Ribbon, click the File tab, and then click Options
  • Click the Proofing category, and then click the AutoCorrect Options button.

To add a text entry:

  • In the Replace box, type a short code that will be easy to remember.
  • In the With box, type the full word or phrase that will replace the short code, after you type it.

For example, I entered the short code: .dd

When I type that on a worksheet, it will be replaced by my full name: Debra Dalgleish

AutoCorrect entry for name

Create AutoCorrect Symbols

In the AutoCorrect Options window, there is a tab for Math AutoCorrect. Unfortunately, those shortcuts don’t work in Excel.

However, you can copy the symbols from that tab, and create regular AutoCorrect entries for them.

This animated screen shot shows the steps – type a short code, copy and paste a symbol from the Math tab, then use the short code on your worksheet, any time you need it.

autocorrectsymbols03

List of Excel AutoCorrect Entries

If you’d like to see everything that’s in your AutoCorrect list, run this macro, written long ago by Dana DeLouis, and he shared it in the Excel newsgroups.

I added a line to insert a new sheet, so you don’t accidentally overwrite anything. The macro lists the short codes (Replace) items in column A, and the full text (With) in column B.

AutoCorrect List

Copy this macro, and paste it into a regular code module.

Sub AutoCorrectEntries_Display()
'// Dana DeLouis
'// Backup AutoCorrect to Worksheet
'2018-01-30 added line to insert new sheet
   Dim ACE
   ACE = Application.AutoCorrect.ReplacementList
   Sheets.Add
   Range(Cells(1), Cells(UBound(ACE), 2)) = ACE
   Columns("A:B").AutoFit
End Sub

Edit the AutoCorrect Entries List

After the list is created on a worksheet, you can manually edit it, before you import the list to a different computer.

For example,

  • add new codes and replacement text
  • modify the replacement text for personal entries that you previously created
  • delete any personal entries that won’t be needed on the other computer

Or, just leave the list as is, then save and close the workbook.

Add AutoCorrect Entries From List

After you create your list of AutoCorrect entries, use the following macro (also by Dana DeLouis)  to add those entries on a different computer.

  • Open the workbook with the list,
  • Activate the sheet that has the exported list of AutoCorrect items.
  • Run the macro below, to add those items to the new computer’s list.

What the macro does:

  • adds any new items
  • overwrites any matching codes
  • does NOT remove any existing entries that are not in the list

Copy this macro, and paste it into a regular code module.

Sub AutoCorrectEntries_Add()
'// Dana DeLouis
'// Add AutoCorrect entries.
'// Column A -> Wrong   Word
'// Column B -> Correct Word
   Dim rng As Range
   With Application.AutoCorrect
      For Each rng In Columns(1) _
      .SpecialCells(xlConstants, _
          xlTextValues).Cells
         .AddReplacement rng, rng.Offset(0, 1)
      Next
   End With
End Sub

_____________________________________

Create and Copy AutoCorrect List Items http://blog.contextures.com/

 

____________________

3 thoughts on “Create and Copy AutoCorrect List Items”

  1. It’s greatly useful, thanks.
    In the sub to add entries, it bugs if there is a space at the end of the word. So I will add a line to remove that blank space.
    Evelyne

  2. Dear Debra, I have a large list of autocorrect words (7117 entries) in a list in excel that I am trying to get to work using code from this site. In column A of my list I have the wrongly-spelled words and in column B I have the correct names. I can’t figure out how to do it. I tried your code listed above as follows:
    Sub AutoCorrectEntries_Add()
    ‘// Dana DeLouis
    ‘// Add AutoCorrect entries.
    ‘// Column A -> Wrong Word
    ‘// Column B -> Correct Word
    Dim rng As Range
    With Application.AutoCorrect
    For Each rng In Columns(1) _
    .SpecialCells(xlConstants, _
    xlTextValues).Cells
    .AddReplacement rng, rng.Offset(0, 1)
    Next
    End With
    End Sub

    However when I run it, it returns an error 1004 at ‘.AddReplacement rng, rng.Offset(0, 1)’.
    Not sure what I am doing wrong – very frustrating!
    Can you help please?
    Regards
    Charles

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.