move range of rows down in vba

Question

I am trying to select the first 7 rows of my spreadsheet (the exact number could vary) and move it 32 rows down (number of rows to move down could also vary). Could someone help me with the code? I have tried:

Worksheets("Report").Cells(x1, 5).EntireRow.Offset(32, 0).Select

I also tried

for i = 1 to 7
set x1 = worksheets("Report").Cells(i, 5)
Rows(x1).EntireRow.Offset(32, 0).Select

Won't work either. Thanks in advance for the help!

Answer 1

This does what you ask, and if there are any rows after 32 shifts them down:

Sub MoveRowsDown()
Dim NumRows As Long
Dim TargetRow As Long
Dim ws As Excel.Worksheet

NumRows = 7 'change as necessary
TargetRow = 33 'change as necessary
Set ws = ActiveSheet ' change as necessary

ws.Range("A1").Resize(NumRows).EntireRow.Cut
ws.Range("A" & TargetRow + NumRows).EntireRow.Insert shift:=xlDown
End Sub

EDIT: Here's a version that just cuts and pastes, with no fancy inserting:

Sub MoveRowsDown()
Dim NumRows As Long
Dim TargetRow As Long
Dim ws As Excel.Worksheet

NumRows = 7 'change as necessary
TargetRow = 33 'change as necessary
Set ws = ActiveSheet ' change as necessary

ws.Range("A1").Resize(NumRows).EntireRow.Cut Destination:=ws.Range("A" & TargetRow)
End Sub