We often need to paste special values or numbers or characters in Excel. Excel allows users to paste different types of data through a feature called Paste Special. However, for some issues, sometimes the paste special option does not work properly. In this article, we will show 4 possible solutions when paste special is not working in Excel.
Download Practice Workbook
You can download the practice workbook here.
4 Possible Solutions If Paste Special Function Is Not Working in Excel
In this article, we will discuss 4 probable solutions to the problem when the paste special is not working properly in Excel. Firstly, we will enable the paste option by going to the Advanced option in Excel. Secondly, we will open the Excel file in safe mode to solve the problem. The last two problems are subjective. If someone is using a VBA code, then he/she needs to set the Application.EnableEvents feature to False in order to fix the problem. Finally, the users may need to close all browsers, if opened, to resolve the matter.
1. Using Advanced Option
It often happens that the paste option button is disabled. Excel has a way to enable it. In this method, we will use the Advanced option in Excel to fix the problem.
- To begin with, select the File tab.
- Then, from the More options select Options.
- Consequently, a prompt will be on the screen.
- From the prompt, first select the Advanced option.
- Then, check the box stating ‘Show Paste Options button when content is pasted’ under the Cut, copy, and paste section.
- Finally, click OK.
- As a result, we will find that different paste option is enabled.
Read More: PasteSpecial Method of Worksheet Class Failed (Reasons & Solutions)
- VBA Paste Special to Copy Values and Formats in Excel (9 Examples)
- How to Apply VBA PasteSpecial and Keep Source Formatting in Excel
- Use Paste Special to Multiply Numbers in Excel
- How to Copy and Paste in Excel Using VBA (7 Methods)
- Copy a Cell in Excel Using Formula(7 Methods)
2. Enabling Safe Mode
A safe mode operation of Excel is where we open Excel in such a way that it allows Excel to prevent any type of errors of operation. It also prevents Excel from opening add-ins. In this method, we will open Excel in safe mode to fix our problem.
- Firstly, press Ctrl.
- Then, upon pressing the button right-click on the file.
- From the available options, select Open.
- As a result, a prompt will appear on the screen.
- Then, select Yes from the prompt.
- After that, open Excel add-ins one by one and try to find the add-in that is causing the problem.
- Consequently, we will see that all the paste options are available in the options.
Read More: How to Use VBA PasteSpecial for Formulas and Formats in Excel (3 Ways)
3. Setting Application.EnableEvents to False
This is a special method dedicated to the situations while we are adding a VBA code to our Excel file using the Developer tab. If we set the Application.EnableEvents to True then, while writing the code Excel will allow different events like copying cells or modifying data to happen which may temper our code. That in turn prevents the users to paste properly. That is why we need to set it to False to avoid such problems.
Application.EnableEvents = False
Read More: Run Time Error 1004: PasteSpecial Method of Range Class Failed
4. Closing Browsers
Sometimes we need to open different tabs in browsers while performing any task in Excel. This may sometimes prevent us from using the paste special feature. So, just as a precaution we may need to close the browsers while working in Excel just to avoid the problem.
Read More: How to Use Paste Special Command in Excel (5 Suitable Ways)
In this article, we have talked about 4 easy ways to resolve the problem when paste special is not working in Excel. These solutions will help users to perform their task properly in Excel and avoid any unwanted disturbance.
- How to Use Paste Name Dialog Box In Excel (3 Ideal Examples)
- Exchange (Copy, Import, Export) Data Between Excel and Access
- How to Make a Copy of an Excel File (3 Useful Methods)
- [Solved:] Copy Paste from Excel to Google Sheets Not Working
- [Fixed!] Copy and Paste Not Working Between Workbooks in Excel
- Macro to Copy Data from One Workbook to Another Based on Criteria
- How to Copy Data from One Cell to Another in Excel Automatically