VBA Copy and paste a range of numbers

Question

Hi There

I'm trying to copy and paste a range, to create a 28 by 28 grid of numbers "rotating" the values so that each time the range is pasted into the next column, the range is moves down by one row and the last value "overflows" back to the top of the next row, I've got this far but am stumped on the overflow part (i' relative newbie to VBA)

Sub Test()

Dim oRange As Range
Set oRange = ActiveSheet.Range("A1:A28") 

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

End Sub

Also I need to copy and paste values and formatting of the cells

Hope you guys can help Thanks

Dan

Answer 1

Sub Test()

    Dim oRange As Range
    Dim startColumn As String
    Dim rangeStart As Integer
    Dim rangeEnd As Integer
    Dim cellCount As Integer
    Dim i As Integer

    startColumn = "A"
    rangeStart = 1
    rangeEnd = 28
    cellCount = rangeEnd - rangeStart + 1

    For i = 1 To cellCount - 1
        Set oRange = ActiveSheet.Range(startColumn & rangeStart & _
                                ":" & startColumn & (rangeEnd - i))
        oRange.Copy
        oRange.Offset(i, i).PasteSpecial xlPasteAll

        Set oRange = ActiveSheet.Range(startColumn & (rangeEnd - i + 1) & _
                                ":" & startColumn & rangeEnd)
        oRange.Copy
        oRange.Offset((-1 * cellCount) + i, i).PasteSpecial xlPasteAll
    Next i

End Sub

EDIT:

to insert a blanck row at index 'i':

Rows(i & ":" & i).Select
Selection.Insert Shift:=xlDown

to insert 5 rows at the top of the worksheet insert a row 5 times at index 1:

For i = 1 To 5
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
Next