On Aug 1, 4:35 pm, Dave Peterson wrote:
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 wrote:

On Jul 31, 3:17 pm, Dave Peterson wrote:
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- 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- 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