CODEWITHSUNDEEP
vbaexcel
batchnewbie
batchnewbie

Reputation: 23

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

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

Upvotes: 2

Views: 20412

Answers (1)

shahkalpesh
shahkalpesh

Reputation: 33484

Replace

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

to

wsCopyTo.Range("A2").Insert xlShiftDown

Upvotes: 5

Related Questions