Excel宏VBA-如何插入复制的单元格而不是粘贴 [英] Excel Macro VBA - How to insert copied cells instead of paste

问题描述

我有一个运行以下代码的宏，用于将数据从一个Excel文件复制到另一个Excel文件.当前，它正在将数据复制并粘贴到第二个Excel文件中.这意味着它将覆盖第二个Excel文件中的所有数据.

I have a macro running with the following code to copy data from one Excel file into another Excel file. Currently it is copying and pasting the data into the second Excel file. This means it overwrites any data that is in the second Excel file.

我希望它插入复制的单元格，而不是粘贴在工作簿中已有的数据上.我应该如何编辑第27行才能使其正常工作?

I would like it to insert the copied cells rather than paste over the data already in the workbook. How should I edit line 27 to make this work?

我认为我需要使用以下代码，但不确定如何将其应用于我的原始代码.

I think I need to use the following code but I'm not sure how to apply it to my original code.

InsertCopiedCells

这是粘贴数据的原始代码.

Here is the original code that is pasting the data.

Const strFile As String = "E:\My Documents\file2\file\MonthlyReports\Data\file1.xlsx" 
'Add the file location

    Dim wbCopyTo    As Workbook
    Dim wsCopyTo    As Worksheet
    Dim wbCopyFrom  As Workbook
    Dim wsCopyFrom  As Worksheet

    Set wbCopyTo = ActiveWorkbook
    Set wsCopyTo = ActiveSheet

    '-------------------------------------------------------------
    'Open file with data to be copied

    Set wbCopyFrom = Workbooks.Open(strFile)
    Set wsCopyFrom = wbCopyFrom.Worksheets(1)

    '--------------------------------------------------------------
    'Copy Range

    wsCopyFrom.Range("A2:AA5000").Copy
    wsCopyTo.Range("A2").PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False

推荐答案

替换

wsCopyTo.Range("A2").PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False

到

wsCopyTo.Range("A2").Insert xlShiftDown

这篇关于Excel宏VBA-如何插入复制的单元格而不是粘贴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！