文章背景： 在Excel中，公式引用无效单元格时将显示 #REF! 错误。当公式所引用的单元格被删除或被粘贴覆盖时最常发生这种情况。
为了防止单元格被人不小心移动，可以通过禁用自动填充功能来实现（参见延伸阅读）。使用VBA代码：Application.CellDragAndDrop = False ，虽然可以避免单元格被移动，但也存在一个问题，如果复制其他工作簿的内容，再回到本工作表内进行粘贴时，发现数据无法粘贴。换句话说，无法在本工作表内进行跨表粘贴。
这是因为CellDragAndDrop = False clears Excel's clipboard as intended。也就是说，Application.CellDragAndDrop = False这条语句运行后，会自动清除剪切板的内容，所以在本工作表内无法粘贴其他工作簿的数据。
为了在禁用自动填充功能的同时，依然可以在本工作表内正常跨表粘贴数据，在查阅相关资料之后，找到了可以实现这样要求的VBA代码。
示例： 在本工作簿内，原始数据保存在sheets("源数据")这张表内。要实现的功能是，在源数据这张表内，禁用自动填充功能之后，仍然可以跨表粘贴数据。
打开VB窗口，在ThisWorkbook内，添加如下代码：
Option Explicit
Private WithEvents AppEvents As Application
'系统兼容性判断'
#If VBA7 Then
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
#Else
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
#End If
'Change this Const as required.
Const SHEET_WHERE_DRAG_N_DROP_IS_DISABLED = "源数据"
Private Sub Workbook_Activate()
Set AppEvents = Application
End Sub
Private Sub AppEvents_WorkbookActivate(ByVal Wb As Workbook)
EnableCellDrafAndDrop = Not (Wb Is ThisWorkbook And ActiveSheet Is Sheets(SHEET_WHERE_DRAG_N_DROP_IS_DISABLED))
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
EnableCellDrafAndDrop = Not (Sh Is Sheets(SHEET_WHERE_DRAG_N_DROP_IS_DISABLED))
End Sub
Private Property Let EnableCellDrafAndDrop(ByVal Enable As Boolean)
On Error GoTo CloseClipbrd
Call OpenClipboard(Application.hwnd)
Application.CellDragAndDrop = Enable
CloseClipbrd:
Call CloseClipboard
End Property代码块输入区域和效果展示：http://mpvideo.qpic.cn/0b78v4aaqaaaseabptpdpbqfbl6dbcxqacaa.f10002.mp4?dis_k=5bd6363fe02591188775485f0fe4b4bd&dis_t=1663655723&vid=wxv_1944565487183396865&format_id=10002&support_redirect=0&mmversion=false
参考资料：
[1] Workbook_Activate and Workbook_Deactivate Events causing Copy and Paste problem between two workbooks（https://social.msdn.microsoft.com/Forums/en-US/4cc5a94e-1931-46b5-82e6-97fe34bc6cc4/workbookactivate-and-workbookdeactivate-events-causing-copy-and-paste-problem-between-two?forum=isvvba）
[2] I can't copy data from one workbook to another due to Application.CellDragAndDrop = False（https://www.mrexcel.com/board/threads/i-cant-copy-data-from-one-workbook-to-another-due-to-application-celldraganddrop-false.1096123/）
[2] CellDragAndDrop & Paste buffer（https://www.mrexcel.com/board/threads/celldraganddrop-paste-buffer.469656/page-2#posts）
延伸阅读：
[1] VBA: 禁止单元格移动，防止单元格公式引用失效
