Can I swap cell info?

[listerheb]

I constantly have to swap content from a group of five cells with another group of five cells. What I have been doing is copying from the first five to a blank five, then taking the second five and copying it to the first, then moving the one I moved at the begining to the second set of five, and finally deleting the left over. Is there a way to select both sets and swap the contents? I must do this over a hundred times in a day.

 

[Yogi Anand]

Not knowing the context in which the worksheet has been created and not knowing the background of why you are swapping the cell values, and so forth, I can not comment about the suitability of your layout -- however, in reference to your latest specification, try the following ...

Sub ySwap4()
    
    '**** two step process ....
    '* Step 1 ... select First range to swap e.g. Z7:AB9
    '****
    Set yRange = Selection
    Debug.Print yRange.Address
    Selection.Copy [[Personal.xls]sheet1!A1]
    
    '**** two step process ....
    '* Step 2 ... key-in cells of range to swap e.g. AH12:AJ14
    '****
    swapRange = InputBox("key-in SwapRange cells")
        Debug.Print swapRange
        Set SwapMe = Range(swapRange)
        SwapMe.Copy yRange
        [[Personal.xls]sheet1!A1:C3].Copy SwapMe
    
    '**** two step process ....
    '* All Done!
    '****
End Sub

Not to interfere with any of the cells in your worksheet, I am storing the value of the first cell to be swapped in cells A1:C3 of Sheet1 of the Personal Workbook -- if this is not feasible choose any other range for this purpose and make adjustments accordingly.