Help in code Steve G wrote to move data from 4 columns to 21 columns

[Steve G]

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]

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

 

[Jim Thomlinson]

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

 

[Steve G]

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]

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]

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

 

[Steve G]

What line causes the error?

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

change the sheet name to what you need.















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dear Mr. Peterson--

The code stops at "With Worksheets("Sheet1")." But the worksheet
itself has the code in it and the workbook/file only has Sheet1.
There are no other sheets in the file/workbook. The VBA project in
the file/workbook is setup as follows:

VBAProject(Filename)
---Microsoft Excel Object
--Sheet7(Sheet1) I do not know what this is--it may be causing the
problem
--This workbook
--modules
----module1--this has the code


Thank you again for taking a look.

Steve G

 

[Dave Peterson]

First, when you saw this Sheet7(Sheet1) in the VBE project explorer window, the
Sheet7 is the codename for the worksheet--it's a name that programmers can use
that is more difficult to change by users. It makes your code a little more
robust.

Sheet1 (the name in ()'s) is the worksheet name. It's the name you see in the
worksheet tab at the bottom of the window when you're in excel (not the VBE).

Second, code like this doesn't belong in the worksheet module--it belongs in a
general module.

Inside the VBE
select your workbook's project
insert|Module
and move the code to that general module. Don't keep the code under the
worksheet module--it'll only serve to confuse later.

=======
But a cheap and dirty fix....(I wouldn't do this!):

With Worksheets("Sheet1")
can be changed to
With Me

(Me is the worksheet that owns the code.)

 

[Steve G]

First, when you saw this Sheet7(Sheet1) in the VBE project explorer window, the
Sheet7 is the codename for the worksheet--it's a name that programmers can use
that is more difficult to change by users. It makes your code a little more
robust.

Sheet1 (the name in ()'s) is the worksheet name. It's the name you see in the
worksheet tab at the bottom of the window when you're in excel (not the VBE).

Second, code like this doesn't belong in the worksheet module--it belongs in a
general module.

Inside the VBE
select your workbook's project
insert|Module
and move the code to that general module. Don't keep the code under the
worksheet module--it'll only serve to confuse later.

=======
But a cheap and dirty fix....(I wouldn't do this!):

With Worksheets("Sheet1")
can be changed to
With Me

(Me is the worksheet that owns the code.)












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Mr. Peterson--

I got it to work. I copied the data and pasted special the data from
the spreadsheet in question to a new spreadsheet called Sheet1 in a
new file. I saved the new file. I deleted the old file which had the
project with the Sheet7(Sheet1). I had the code for moving the data
in my personal macro also. I ran the macro/code with my new file.
As usual my personal file was open--the code worked from my personal
macro applied to my new file and new worksheet. I did this because I
did not know how to get rid of that Sheet 7(Sheet1) in Project
Explorer other than deleting the file with that project in it.

Thank you very much--thank you for hanging in there. Sincerely,

Steve G