• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 52
  • Last Modified:

Excel VBA: New range if copied to new location.

What VBA code would I use to determine the new range if a user copied/pasted the range to a new location. (See Examples)


Starting Range: C20:F30
User Moves Range to: H4
Solution: In this case, if the user cut C20:F30 and pasted it into cell H4 the new range would be: H4:K14.


Starting Range: AB42:AD60
User Moves Range to:DD50
Solution in this case, if the user cut AB42:AD60 and pasted it to cell DD50 the new range would be: DD50:DF68


Starting Range: AB42:AD60
User Moves Range to:A7
Solution in this case, if the user cut AB42:AD60 and pasted it to cell A7 the new range would be: A7:C25
2 Solutions
NorieVBA ExpertCommented:
To get the new range you could resize the destination range by the no of columns/rows in the source range, something like this.
Dim rngSrc As Range
Dim rngDst As Range
Dim rngNew As Range

    Set rngSrc = Range("AB42:AD60")
    Set rngDst = Range("A7")

    rngSrc.Copy  rngDst

   Set rngNew = rngDst.Resize(rngSrc.Rows.Count, rngSrc.Columns.Count)

   MsgBox rngNew.Address(0,0)

Open in new window

How is the copying/pasting being done?
Fabrice LambertFabrice LambertCommented:
Maybe by using the worksheet change and worksheetSelectionChange event.
Try the following in a worksheet module:
Option Explicit

Private moveDetection As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    moveDetection = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If moveDetection Then
        moveDetection  = False
        Debug.Print "Range moved to: " & rng.Address
    End If
End Sub

Open in new window

ouestqueAuthor Commented:
Thank You! I didn't even know there was a 're-size' function. Pretty cool!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now