Excel VBA运行时错误1004 [英] Excel VBA runtime error 1004

问题描述

这个宏直接来使用Excel提供的记录宏。

  Sub paste_formulas（）
 Selection.PasteSpecial粘贴：= xlPasteFormulas ，操作：= xlNone，_ 
 SkipBlanks：= False，Transpose：= False 
 End Sub 
  

以下是事件顺序：

1. 手动选择CSV文件A中的单元格范围，并复制（CTRL + C ）


2. 切换到XLS文件B，然后选择我想要复制的数据粘贴的单元格。


3. 按宏并运行personal.xlsm！粘贴_公式

这是当我收到错误。为什么在复制/粘贴特殊公式时手动进行操作？但是在宏中失败？

注意：我需要这个工作，无论选择我会复制的范围（将随时间变化），以及不管我将公式粘贴到的位置（也将随时间变化）。换句话说，用于复制和/或粘贴的固定范围的硬编码对我来说不起作用。

提前感谢任何帮助，了解为什么我的代码不工作或者提供一个解决方案。

解决方案

原因很简单，当您从CSV复制然后在您的工作簿中，点击开发工具栏中的 Macros ，Excel会清除剪贴板。

Excel具有清除剪贴板的习惯，当您点击开发人员|宏。为了演示这一点，请从相同的工作簿中复制单元格。你会看到蚂蚁像细胞周围的边界。现在在同一个工作簿中，点击Developer |宏。 Ant像边框将消失：）

为您的宏设置一个快捷键并使用它。它将工作：）

I'm trying to write a macro to paste special formulas but keep getting a runtime error 1004 "PasteSpecial method of Range class failed".

This macro comes directly from using the "Record Macro" provided by Excel.

Sub paste_formulas()
  Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
         SkipBlanks:=False, Transpose:=False
End Sub

Here is the sequence of events:

1. Manually select a range of cells in CSV file A and copy (CTRL+C).
2. Switch to the XLS file B and select the cell were I want the copied data pasted.
3. Press Macros and run personal.xlsm!paste_formulas

That's when I get the error. Why does this work manually when I copy/paste-special formulas, but fail in the macro?

Note: I need this to work in the above sequence regardless of the selected range that I copy (will vary from time to time), and regardless of the location I paste formulas to (will also vary from time to time). In other words, hardcoding a fixed range for copy and/or paste won't work for me.

Thanks in advance for any help understanding why my code isn't working or providing a work-around.

解决方案

The reason is very simple, When you copy from the CSV and then in your workbook, click on Macros in the Developer Toolbar, Excel clears the clipboard.

Excel has this habit of clearing the clipboard when you click on Developer | Macros. To demonstrate this, copy the cells from the same workbook. You will see the ant like border around the cells. Now in the same workbook, click on Developer | Macros. The Ant like borders will disappear :)

Set a shortcut key for your macro and use that. it will work :)

这篇关于Excel VBA运行时错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！