Swapping Ranges Question

emilk570

New Member
Joined
Feb 6, 2016
Messages
2
I am currently in the process of creating a macro that will swap two ranges on one sheet and then swap the same ranges on a different sheet automatically. The macro works and I am able to swap the first 2 ranges on the first sheet with this macro but then I have to manually enter the second sheet's ranges to swap them. Any thoughts?

Sub Swap()


Dim Rng1 As Range, Rng2 As Range
Dim arr1 As Variant, arr2 As Variant
xTitleId = "Swap"
Set Rng1 = Application.Selection
Set Rng1 = Application.InputBox("1 (Select Range):", xTitleId, Rng1.Address, Type:=8)
Set Rng2 = Application.InputBox("2 (Select Range):", xTitleId, Type:=8)
Application.ScreenUpdating = False
arr1 = Rng1.Value
arr2 = Rng2.Value
Rng1.Value = arr2
Rng2.Value = arr1


Sheets("Sheet 2").Select
xTitleId = "Swap"
Set Rng1 = Application.Selection
Set Rng1 = Application.InputBox("1 (Select Range):", xTitleId, Rng1.Address, Type:=8)
Set Rng2 = Application.InputBox("2 (Select Range):", xTitleId, Type:=8)
Application.ScreenUpdating = False
arr1 = Rng1.Value
arr2 = Rng2.Value
Rng1.Value = arr2
Rng2.Value = arr1


Sheets("Sheet 1").Select
Application.ScreenUpdating = True
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,078
Perhaps this is what you want

Code:
Sub Swap()
    Dim Rng1 As Range, Rng2 As Range
    Dim arr1 As Variant, arr2 As Variant
    xTitleId = "Swap"
    
    On Error Resume Next
    Set Rng1 = Application.InputBox("1 (Select Range):", xTitleId, Selection.Address, Type:=8)
        If Rng1 Is Nothing Then Exit Sub: Rem cancel pressed
    Set Rng2 = Application.InputBox("2 (Select Range):", xTitleId, Type:=8)
        If Rng2 Is Nothing then Exit Sub: Rem canceled
    On Error Goto 0
    Application.ScreenUpdating = False

    arr1 = Rng1.Value
    arr2 = Rng2.Value
    Rng1.Value = arr2
    Rng2.Value = arr1

    With Sheets("Sheet2")
        arr1 = .Range(Rng1.Address).Value
        arr2 = .Range(Rng2.Address).Value
        .Range(Rng1.Address).Value = arr2
        .Range(Rng2.Address)Value = arr1
    End With

     Application.ScreenUpdating = False
End Sub
 
Last edited:

Forum statistics

Threads
1,144,369
Messages
5,723,952
Members
422,528
Latest member
IMK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top