如何简化vba复制粘贴单独的单元格值

Question

我知道有一种方法可以使这段代码简短而快速，但似乎我是一个新手，无法实现它。我有一些合并的单元格(这是一种形式，它应该是"标准"，但用户不断搞乱它，我使用的宏会给大量的错误，不会收集信息)。到目前为止，我所做的就是编写另一段代码，再次打开"标准"表单，并填充用户表单中的所有数据。

它像一个魅力，但现在我厌倦了寻找和询问周围，因为我不能弄清楚如何简化它(我知道它一定是愚蠢的你，但我不能接受的事实，它不能做到-我也不能接受，"所有强大的"VBA必须花很长时间为这么小的事情写这么多的代码)。

Application.ScreenUpdating = False
ActiveWindow.ActivatePrevious
Workbooks.Open Filename:="...Standard Formular.xls"
Sheets("Formulaire - Form").Select
ActiveWindow.ActivateNext
Range("E9").Copy
ActiveWindow.ActivatePrevious
Range("E9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E11").Copy
ActiveWindow.ActivatePrevious
Range("E11").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E13").Copy
ActiveWindow.ActivatePrevious
Range("E13").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E15").Copy
ActiveWindow.ActivatePrevious
Range("E15").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E17").Copy
ActiveWindow.ActivatePrevious
Range("E17").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E19").Copy
ActiveWindow.ActivatePrevious
Range("E19").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("E21").Select
ActiveCell.FormulaR1C1 = Date
ActiveWindow.ActivateNext
Range("E28").Copy
ActiveWindow.ActivatePrevious
Range("E28").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E30").Copy
ActiveWindow.ActivatePrevious
Range("E30").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E32").Copy
ActiveWindow.ActivatePrevious
Range("E32").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E34").Copy
ActiveWindow.ActivatePrevious
Range("E34").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E36").Copy
ActiveWindow.ActivatePrevious
Range("E36").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E38").Copy
ActiveWindow.ActivatePrevious
Range("E38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E48").Copy
ActiveWindow.ActivatePrevious
Range("E48").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E50").Copy
ActiveWindow.ActivatePrevious
Range("E50").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E52").Copy
ActiveWindow.ActivatePrevious
Range("E52").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E54").Copy
ActiveWindow.ActivatePrevious
Range("E54").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E63").Copy
ActiveWindow.ActivatePrevious
Range("E63").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("G63").Copy
ActiveWindow.ActivatePrevious
Range("G63").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("C65").Copy
ActiveWindow.ActivatePrevious
Range("C65").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("D65").Copy
ActiveWindow.ActivatePrevious
Range("D65").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E65").Copy
ActiveWindow.ActivatePrevious
Range("E65").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("F65").Copy
ActiveWindow.ActivatePrevious
Range("F65").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("G65").Copy
ActiveWindow.ActivatePrevious
Range("G65").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E67").Copy
ActiveWindow.ActivatePrevious
Range("E67").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("A72").Copy
ActiveWindow.ActivatePrevious
Range("A72").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("G72").Copy
ActiveWindow.ActivatePrevious
Range("G72").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Range("E97").Copy
ActiveWindow.ActivatePrevious
Range("E97").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
ActiveWindow.Close
Application.ScreenUpdating = True

提前感谢您抽出时间来帮助我!

Answer 1

将要复制的表单存储在变量中:

wsU = Sheets("Sheet name on user form goes here")
Workbooks.Open Filename:="...Standard Formular.xls"
wsF = Sheets("Formulaire - Form")

现在，不要在激活的工作表之间切换也不要使用复制粘贴，因为这涉及到剪贴板，并且当您在运行宏时开始尝试复制粘贴到另一个应用程序时可能会出错，请尝试:

wsF.Range("E9").value = wsU.Range("E9").value

等

顺便说一下，它的简写是

wsF.[E9] = wsU.[E9]

Answer 2

Do a

`sheet("Where i want to paste").range("xxx").value=sheet("Data im copiing").range("yyy").value`

例如

，您的第一个副本将是:

Sheets("不知道名字").range("E9")。value=Sheets("Formulaire - Form").Range("E9").value