Copy, past special values, offset 1 column for multiple ranges.

[Rysieks]

I am trying to take multiple ranges and essentially copy, paste special values one column over to the left. I’ve tried a few approaches, the lines with apostrophe in front are my failed attempts. Any help is much appreciated.

Sub CopyRange_MoveLeft ()
 
Dim rngUnion As Range, rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, _
rng5 As Range, rng6 As Range, rng7 As Range, rng8 As Range, rng9 As Range, rng10 As Range
Dim rng As Range
 
Set rng1 = Range("D3")
Set rng2 = Range("D9:G10")
Set rng3 = Range("D14:G15")
Set rng4 = Range("D19:G20")
Set rng5 = Range("D24:G25")
Set rng6 = Range("D29:G30")
Set rng7 = Range("D34:G35")
Set rng8 = Range("D39:G40")
Set rng9 = Range("D44:G45")
Set rng10 = Range("D19:G20")
Set rngUnion = Union(rng1, rng2, rng3, rng4, rng5, rng6, rng7, rng8, rng9, rng10)
 
‘ Failed attempts below…
'rngUnion.Copy Destination:=ActiveSheet.Cells.Offset(0, -1)
 
'rng.Copy Destination:=Cells.Offset(0, -1)
 
'For Each rng In rngUnion
'    .Cut Destination:=.Offset(0, -1)
'    Next
   
End Sub

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

You can step through each Area of a non-contiguous range like this...

Dim iArea As Integer

For iArea = 1 To rngUnion.Areas.Count
   With rngUnion.Areas(iArea).Cells
      .Copy
      .Offset(0, -1).PasteSpecial (xlPasteValues)
   End With
Next iArea

 

[Rysieks]

Brilliant! Worked great. Thank you !!