I am new to VBA. Below is code I wrote to move data in cells from 9
columns to 31 columns. I need to do this operation about 135 times
every two weeks with new data. I get the error message "Run -Time
error '1004' Method 'Range' of object '_Global' failed.

I would appreciate help with the set r1, r2, r3,r4,r5,r6 ranges.
Thank you.

Steve G


Sub MoveRangePayroll()

Dim r1 As Range 'Range of 1st iteration--data to be moved from
Dim r2 As Range 'Range of 1st iteration--data will be moved to
Dim r3 As Range 'Range of 2nd iteration--data to be moved from
Dim r4 As Range 'Range of 2nd iteration--data will be moved to
Dim r5 As Range 'Range of 3rd iteration--data to be moved from
Dim r6 As Range 'Range of 3rd iteration--data will be moved to
Dim dataRnge As Range 'range of all the data to be moved
Dim i As Integer 'number of sets of data equal to number of paid
staff--i is a counter

Set dataRnge = Range("A1:J28")

Worksheets("Sheet1").Select
With dataRnge

i = 1
For i = 1 To 25 Step 4

Set r1 = Range("B(i+1):I(i+3)")
Set r2 = Range("J(i):Q(i+2)")
Set r3 = Range("J(i+1):Q(i+2)")
Set r4 = Range("Ri:Y(i+1)")
Set r5 = Range("S(i+1):Z(i+1)")
Set r6 = Range("AAi:AEi")

Range(r1).Cut Range(r2)
Range(r3).Cut Range(r4)
Range(r5).Cut Range(r6)

i = i + 4

Next

End With
End Sub

Try changing these lines from something like:

Set r1 = Range("B(i+1):I(i+3)")
Set r2 = Range("J(i):Q(i+2)")
....
to
Set r1 = range(cells(i+1,"B"),cells(i+3,"I"))
Set r2 = Range(cells(i,"j"),cells(i+2,"Q"))
....

And then since r1,r2,... are already ranges, you change this:

Range(r1).Cut Range(r2)
to
r1.Cut r2




Steve G wrote:

I am new to VBA. Below is code I wrote to move data in cells from 9
columns to 31 columns. I need to do this operation about 135 times
every two weeks with new data. I get the error message "Run -Time
error '1004' Method 'Range' of object '_Global' failed.

I would appreciate help with the set r1, r2, r3,r4,r5,r6 ranges.
Thank you.

Steve G

Sub MoveRangePayroll()

Dim r1 As Range 'Range of 1st iteration--data to be moved from
Dim r2 As Range 'Range of 1st iteration--data will be moved to
Dim r3 As Range 'Range of 2nd iteration--data to be moved from
Dim r4 As Range 'Range of 2nd iteration--data will be moved to
Dim r5 As Range 'Range of 3rd iteration--data to be moved from
Dim r6 As Range 'Range of 3rd iteration--data will be moved to
Dim dataRnge As Range 'range of all the data to be moved
Dim i As Integer 'number of sets of data equal to number of paid
staff--i is a counter

Set dataRnge = Range("A1:J28")

Worksheets("Sheet1").Select
With dataRnge

i = 1
For i = 1 To 25 Step 4

Set r1 = Range("B(i+1):I(i+3)")
Set r2 = Range("J(i):Q(i+2)")
Set r3 = Range("J(i+1):Q(i+2)")
Set r4 = Range("Ri:Y(i+1)")
Set r5 = Range("S(i+1):Z(i+1)")
Set r6 = Range("AAi:AEi")

Range(r1).Cut Range(r2)
Range(r3).Cut Range(r4)
Range(r5).Cut Range(r6)

i = i + 4

Next

End With
End Sub

--

Dave Peterson

Give this a try. it should be closer...

Sub MoveRangePayroll()

Dim r1 As Range 'Range of 1st iteration--data to be moved from
Dim r2 As Range 'Range of 1st iteration--data will be moved to
Dim r3 As Range 'Range of 2nd iteration--data to be moved from
Dim r4 As Range 'Range of 2nd iteration--data will be moved to
Dim r5 As Range 'Range of 3rd iteration--data to be moved from
Dim r6 As Range 'Range of 3rd iteration--data will be moved to
Dim dataRnge As Range 'range of all the data to be moved
Dim i As Integer 'number of sets of data equal to number of paid
staff--i is a counter

Set dataRnge = Range("A1:J28") 'what is this for???

Worksheets("Sheet1").Select 'Not necessary
'With dataRnge 'you never refence this
With Worksheets("Sheet1")
For i = 1 To 25 Step 4

Set r1 = .Range(.Cells(i+1, "B"), .Cells(i+3, "I"))
Set r2 = .Range(.Cells(i, "J"), .Cells(i+2, "Q"))
Set r3 = .Range(.Cells(i+1, "J"), .Cells(i+2, "Q"))
Set r4 = .Range(.Cells(i, "R"), .Cells(i+1, "Y"))
Set r5 = .Range(.Cells(i+1, "S"), .Cells(i+1, "Z"))
Set r6 = .Range(.Cells(i, "AA"), .Cells(i, "AE"))

r1.Cut r2
r3.Cut r4
r5.Cut r6

Next i

End With
End Sub

--
HTH...

Jim Thomlinson


"Steve G" wrote:

I am new to VBA. Below is code I wrote to move data in cells from 9
columns to 31 columns. I need to do this operation about 135 times
every two weeks with new data. I get the error message "Run -Time
error '1004' Method 'Range' of object '_Global' failed.

I would appreciate help with the set r1, r2, r3,r4,r5,r6 ranges.
Thank you.

Steve G


Sub MoveRangePayroll()

Dim r1 As Range 'Range of 1st iteration--data to be moved from
Dim r2 As Range 'Range of 1st iteration--data will be moved to
Dim r3 As Range 'Range of 2nd iteration--data to be moved from
Dim r4 As Range 'Range of 2nd iteration--data will be moved to
Dim r5 As Range 'Range of 3rd iteration--data to be moved from
Dim r6 As Range 'Range of 3rd iteration--data will be moved to
Dim dataRnge As Range 'range of all the data to be moved
Dim i As Integer 'number of sets of data equal to number of paid
staff--i is a counter

Set dataRnge = Range("A1:J28")

Worksheets("Sheet1").Select
With dataRnge

i = 1
For i = 1 To 25 Step 4

Set r1 = Range("B(i+1):I(i+3)")
Set r2 = Range("J(i):Q(i+2)")
Set r3 = Range("J(i+1):Q(i+2)")
Set r4 = Range("Ri:Y(i+1)")
Set r5 = Range("S(i+1):Z(i+1)")
Set r6 = Range("AAi:AEi")

Range(r1).Cut Range(r2)
Range(r3).Cut Range(r4)
Range(r5).Cut Range(r6)

i = i + 4

Next

End With
End Sub

Dear Mr. Thomlinson and Mr. Peterson--

Thank you for your assistance.

I tried the changes to the code you provided me for moving data in
cells from 9 columns to 31 columns. Below is the revised code. I get
run-time error '424' at "r3.Cut r4." Also my range is A1:I620. I
tried to reflect that in the "For Statement." I would greatly
appreciate your evaluation and input. Gratefully--Steve G


Option Explicit

Sub MoveRangePayroll()

Dim r1 As Range 'Range of 1st iteration--data to be moved from
Dim r2 As Range 'Range of 1st iteration--data will be moved to
Dim r3 As Range 'Range of 2nd iteration--data to be moved from
Dim r4 As Range 'Range of 2nd iteration--data will be moved to
Dim r5 As Range 'Range of 3rd iteration--data to be moved from
Dim r6 As Range 'Range of 3rd iteration--data will be moved to
Dim i As Integer 'number of sets of data equal to number of paid
staff--i is a counter


With Worksheets("Sheet1")

i = 1
For i = 1 To 617 Step 4

Set r1 = .Range(.Cells(i + 1, "B"), .Cells(i + 3, "I"))
Set r2 = .Range(.Cells(i, "J"), .Cells(i + 2, "Q"))
Set r3 = .Range(.Cells(i + 1, "J"), .Cells(i + 2, "Q"))
Set r4 = .Range(.Cells(i, "R"), .Cells(i + 1, "Y"))
Set r5 = .Range(.Cells(i + 1, "S"), .Cells(i + 1, "Z"))
Set r6 = .Range(.Cells(i, "AA"), .Cells(i, "AE"))

r1.Cut r2
r3.Cut r4
r5.Cut r6

i = i + 4

Next

End With
End Sub

Maybe...

Option Explicit

Sub MoveRangePayroll()

Dim r1 As Range 'Range of 1st iteration--data to be moved from
Dim r2 As Range 'Range of 1st iteration--data will be moved to
Dim r3 As Range 'Range of 2nd iteration--data to be moved from
Dim r4 As Range 'Range of 2nd iteration--data will be moved to
Dim r5 As Range 'Range of 3rd iteration--data to be moved from
Dim r6 As Range 'Range of 3rd iteration--data will be moved to
Dim i As Long 'number of sets of data equal to number
'of paid staff--i is a counter


With Worksheets("Sheet1")
For i = 1 To 617 Step 4
Set r1 = .Range(.Cells(i + 1, "B"), .Cells(i + 3, "I"))
Set r2 = .Range(.Cells(i, "J"), .Cells(i + 2, "Q"))
r1.Cut r2

Set r3 = .Range(.Cells(i + 1, "J"), .Cells(i + 2, "Q"))
Set r4 = .Range(.Cells(i, "R"), .Cells(i + 1, "Y"))
r3.Cut r4

Set r5 = .Range(.Cells(i + 1, "S"), .Cells(i + 1, "Z"))
Set r6 = .Range(.Cells(i, "AA"), .Cells(i, "AE"))
r5.Cut r6
Next i

End With
End Sub

You don't need to adjust the i inside the loop. That's what your Step is for.
And there's no need to make it equal to 1 to start.



Steve G wrote:

Dear Mr. Thomlinson and Mr. Peterson--

Thank you for your assistance.

I tried the changes to the code you provided me for moving data in
cells from 9 columns to 31 columns. Below is the revised code. I get
run-time error '424' at "r3.Cut r4." Also my range is A1:I620. I
tried to reflect that in the "For Statement." I would greatly
appreciate your evaluation and input. Gratefully--Steve G

Option Explicit

Sub MoveRangePayroll()

Dim r1 As Range 'Range of 1st iteration--data to be moved from
Dim r2 As Range 'Range of 1st iteration--data will be moved to
Dim r3 As Range 'Range of 2nd iteration--data to be moved from
Dim r4 As Range 'Range of 2nd iteration--data will be moved to
Dim r5 As Range 'Range of 3rd iteration--data to be moved from
Dim r6 As Range 'Range of 3rd iteration--data will be moved to
Dim i As Integer 'number of sets of data equal to number of paid
staff--i is a counter

With Worksheets("Sheet1")

i = 1
For i = 1 To 617 Step 4

Set r1 = .Range(.Cells(i + 1, "B"), .Cells(i + 3, "I"))
Set r2 = .Range(.Cells(i, "J"), .Cells(i + 2, "Q"))
Set r3 = .Range(.Cells(i + 1, "J"), .Cells(i + 2, "Q"))
Set r4 = .Range(.Cells(i, "R"), .Cells(i + 1, "Y"))
Set r5 = .Range(.Cells(i + 1, "S"), .Cells(i + 1, "Z"))
Set r6 = .Range(.Cells(i, "AA"), .Cells(i, "AE"))

r1.Cut r2
r3.Cut r4
r5.Cut r6

i = i + 4

Next

End With
End Sub

--

Dave Peterson

Dear Mr. Peterson/Mr. Thomlinson--

I realized my range r5 should have had a 'R' instead of a 'S' in the
beginning cell. So I changed it. It had ran before with the 'S' but
one of the numbers was not being moved. Now the macro will not work.
I get error 9 subscript out of range. Here is the revised code.
Please help me if you would. Thank you. Steve G

Option Explicit


Sub MoveRangePayroll()


Dim r1 As Range 'Range of 1st iteration--data to be moved from
Dim r2 As Range 'Range of 1st iteration--data will be moved to
Dim r3 As Range 'Range of 2nd iteration--data to be moved from
Dim r4 As Range 'Range of 2nd iteration--data will be moved to
Dim r5 As Range 'Range of 3rd iteration--data to be moved from
Dim r6 As Range 'Range of 3rd iteration--data will be moved to
Dim i As Long 'number of sets of data equal to number
'of paid staff--i is a counter


With Worksheets("Sheet1")
For i = 1 To 605 Step 4
Set r1 = .Range(.Cells(i + 1, "B"), .Cells(i + 3, "I"))
Set r2 = .Range(.Cells(i, "J"), .Cells(i + 2, "Q"))
r1.Cut r2


Set r3 = .Range(.Cells(i + 1, "J"), .Cells(i + 2, "Q"))
Set r4 = .Range(.Cells(i, "R"), .Cells(i + 1, "Y"))
r3.Cut r4


Set r5 = .Range(.Cells(i + 1, "R"), .Cells(i + 1, "W"))
Set r6 = .Range(.Cells(i, "Z"), .Cells(i, "AE"))
r5.Cut r6
Next i


End With
End Sub

What line causes the error?

If it's this one:
With Worksheets("Sheet1")

change the sheet name to what you need.

Steve G wrote:

Dear Mr. Peterson/Mr. Thomlinson--

I realized my range r5 should have had a 'R' instead of a 'S' in the
beginning cell. So I changed it. It had ran before with the 'S' but
one of the numbers was not being moved. Now the macro will not work.
I get error 9 subscript out of range. Here is the revised code.
Please help me if you would. Thank you. Steve G

Option Explicit

Sub MoveRangePayroll()

Dim r1 As Range 'Range of 1st iteration--data to be moved from
Dim r2 As Range 'Range of 1st iteration--data will be moved to
Dim r3 As Range 'Range of 2nd iteration--data to be moved from
Dim r4 As Range 'Range of 2nd iteration--data will be moved to
Dim r5 As Range 'Range of 3rd iteration--data to be moved from
Dim r6 As Range 'Range of 3rd iteration--data will be moved to
Dim i As Long 'number of sets of data equal to number
'of paid staff--i is a counter

With Worksheets("Sheet1")
For i = 1 To 605 Step 4
Set r1 = .Range(.Cells(i + 1, "B"), .Cells(i + 3, "I"))
Set r2 = .Range(.Cells(i, "J"), .Cells(i + 2, "Q"))
r1.Cut r2

Set r3 = .Range(.Cells(i + 1, "J"), .Cells(i + 2, "Q"))
Set r4 = .Range(.Cells(i, "R"), .Cells(i + 1, "Y"))
r3.Cut r4

Set r5 = .Range(.Cells(i + 1, "R"), .Cells(i + 1, "W"))
Set r6 = .Range(.Cells(i, "Z"), .Cells(i, "AE"))
r5.Cut r6
Next i

End With
End Sub

--

Dave Peterson