Excel Macro VBA - How to insert copied cells instead of paste

Question

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.

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

shahkalpesh · Accepted Answer · 2014-09-13 14:33:54Z

5

Replace

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

to

wsCopyTo.Range("A2").Insert xlShiftDown

answered Sep 13, 2014 at 14:33

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel Macro VBA - How to insert copied cells instead of paste

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related