How to copy data and paste it directly to the right of it without deleting the data that already exi-CodePudding

I have 3 columns of data BX, BY, BZ, which once refreshed generate the latest data from another file. I would like to have the latest data in BX, BY, and BZ copied and pasted next to it in CA, CB, and CC, since there will already be data in CA, CB, and CC I am trying to find a way to paste where the existing data In CA:CC does not get lost, but gets pushed 3 columns to the right, and pasted as values so there is no issue with the formulas that exist in BX, BY, BZ.

I currently have a macro that copies the latest data from BX, BY, and BZ and pastes it as values in the first blank columns it finds, but am not sure how to go about modifying it to get it to paste the latest data in CA:CC and push the older existing data to the right.

Any guidance is greatly appreciated!!

Sub CopyLatest()


' CopyLatest Macro


' Keyboard Shortcut: Ctrl Shift L


    Const Cols As String = "BX:BZ"

   

    Dim ws As Worksheet: Set ws = ActiveSheet
   

    With ws.UsedRange

        Dim srg As Range: Set srg = Intersect(.Cells, ws.Columns(Cols))

        Dim drg As Range: Set drg = .Resize(, srg.Columns.Count) _

            .Offset(, .Columns.Count)

        drg.Value = srg.Value

    End With

      

End Sub

CodePudding user response：

You could try something like this, which will insert three new columns and shift everything else to the right.

 Columns("CA:CC").Select
    Range("CC1").Activate
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

from there you can continue with your existing code and paste the data into the new columns.

CodePudding user response：

Copy 3 specified columns to another range, shifting existing to right:

Sub CopyLatest()
    'Copy columns BX,BY and BZ
    Columns("BX:BZ").Copy
    'Insert from copy buffer, shifting existing to right
    Columns("CA:CA").Insert Shift:=xlToRight
End Sub

CodePudding user response：

You can use a general sub like this:

Sub backupColumnsToTheRight(rgSource As Range)

Dim cntColumnsToCopy As Long
cntColumnsToCopy = rgSource.Columns.Count

Dim rgTarget As Range

With rgSource
    'insert new columns
    .Offset(, cntColumnsToCopy).Resize(, cntColumnsToCopy).Insert xlShiftToRight
    
    'define new target range
    Set rgTarget = .Offset(, cntColumnsToCopy).Resize(, cntColumnsToCopy)

    'use direct copy without select
    .Copy rgTarget
End With
End Sub

You would call this from your main routine like this

Sub CopyLatest()

Const colToCopy As String = "BX:BZ"
Dim rgSource As Range
Set rgSource = ActiveSheet.Range(colToCopy)

backupColumnsToTheRight rgSource

End Sub

CodePudding user response：

In a select the data to be copied as below:

Step 1: In this pic, I want to copy "Date and Paid Amount" into another sheet. Select the 3 columns of data, which you want to transfer, and click copy or Ctrl C

Step 2: I would paste it before "Age" column, so that to see who has paid how much with their age and agreed fees, in the next column.

Right click "Age" column heading, and click "Insert Copied Cell"

Here is your result,,,,

Your data has been pasted, and "Age" and "Fees" column has been shifted