New MemberJoinedJan 29, 2008Messages37
- May 2, 2008
- #1Excel (at least the version I have) does not allow you to copy/paste (or paste-special) multiple selections with one command. That is, I want to be able to select multiple cells in one worksheet (they are adjacent) and copy them to multiple cells in another worksheet where they are NOT adjacent. But Excel does not allow me to do this with one copy/paste command; must be done on each single cell/selection.
Is there a 'work-around' to do this? Or any unique VBA code that would significantly reduce the number of copy/paste commands needed to do this?
Any help much appreciated.
Well-known MemberJoinedJan 15, 2008Messages3,546Office Version
- May 2, 2008
- #2It would have to be like this:
Code:Public Sub doMultiCopy() If TypeName(Selection) <> "Range" Then Exit Sub Dim rngDest As Excel.Range Dim i As Long Dim vRegions As Variant Dim rngRegions() As Excel.Range vRegions = Split(Selection.Address, ",") ReDim rngRegions(LBound(vRegions) To UBound(vRegions)) Set rngDest = Application.InputBox("Select location", "Multi-paste", , , , , , 8) For i = LBound(vRegions) To UBound(vRegions) Set rngRegions(i) = Range(vRegions(i)) rngRegions(i).Copy _ Destination:=rngDest.Offset(rngRegions(i).Row - rngRegions(LBound(rngRegions)).Row, _ rngRegions(i).Column - rngRegions(LBound(rngRegions)).Column) Next i End Sub
You might have to make some modifications to account for different sheets, etc.