0

I am trying to write VBA code that will select a named range, copy it and paste it for a certain number of rows. What I need to know is how to select the range of cells corresponding to the ones above.

E.g. I have a range "myRange" which refers to: "=$A$1:D$1$,$F$1,$K$1". I want to copy this and paste it in "=$A$2:D$2$,$F$2,$K$2" by referring to "myRange" in stead of to the string of cell references.

Any help?

Tony Toews
  • 7,850
  • 1
  • 22
  • 27
Karl
  • 5,573
  • 8
  • 50
  • 73

1 Answers1

1

Something like this?

Sub Test()

    Dim oRange As Range
    Set oRange = ActiveSheet.Range("A1:D1") ' Change this to point at the range to be copied

    Dim i As Integer
    For i = 1 To 10
        oRange.Copy
        oRange.Offset(i, 0).PasteSpecial xlPasteAll
    Next i

End Sub

EDIT: OK, something like this then (to cope with disjoint ranges):

Sub Test()

    Dim oRange As Range
    Set oRange = ActiveSheet.Range("A1,C1:D1") ' Change this to point at the range to be copied

    Dim i As Integer
    For i = 1 To 10
        Dim oArea As Range
        For Each oArea In oRange.Areas
            oArea.Copy
            oArea.Offset(i, 0).PasteSpecial xlPasteAll
        Next oArea
    Next i

End Sub
Gary McGill
  • 26,400
  • 25
  • 118
  • 202