VB code to Insert copied cells instead of paste

[davidbrugge]

Hello,

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.

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.

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">InsertCopiedCells</code>

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

 

[JoeMo]

Maybe insert empty cells then paste to them:

With wsCopyTo
.range("A2:AA5000").Insert shift:= xlDown
wsCopyFrom.Range("A2:AA5000").Copy
.Range("A2").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With