changing worksheets in excel causes references to change so try the following

Private Sub CreateClientSheet_Click()
Dim CurrentCell As Object
Dim intRow, intCol, RowCount As Integer
Dim usedRng As Range
DetermineUsedRange usedRng
RowCount = usedRng.Cells.Count

For intRow = 19 To RowCount Step 1
Sheets("Equipment List").Select
activesheet.Range("B" & intRow, "E" & intRow).Select
If activesheet.Range("D" & intRow) = "Subtotal" Or activesheet.Range("B" &
intRow) = "Grand Total" Then
activesheet.Range("B" & intRow, "G" & intRow).Select
End If
Selection.Copy
Worksheets("Sheet1").Range("A" & intRow).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Worksheets("Sheet1").Range("A" & intRow).PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Next intRow
Sheets("Equipment List").Select
activesheet.Range("A1").Select
End Sub






--
When you lose your mind, you free your life.


"Mike Basden" wrote:


Hi all,

I was wondering if someone could give me some pointers. I've been
struggling with this little snippet of code for a little while and
haven't made much progress.

I keep getting
"Run-time error '1004':
Select method of Range class failed "
at the Range("A" & intRow).Select line.

I've also tried Activate, a direct cell with Offset, etc. all without
much success.

It's a simple little macro, that basically goes through a series of
rows in a sheet and copies selected cell values and formats to another
sheet in the same workbook. Basically it creates a 'clean' client
verion of the information. I must be missing something. Help?
Thanks!

Cheers,
Mike

------------------------------------------
Private Sub CreateClientSheet_Click()
Dim CurrentCell As Object
Dim intRow, intCol, RowCount As Integer
Dim usedRng As Range
DetermineUsedRange usedRng
RowCount = usedRng.Cells.Count

For intRow = 19 To RowCount Step 1
Sheets("Equipment List").Select
Range("B" & intRow, "E" & intRow).Select
If Range("D" & intRow).Value = "Subtotal" Or Range("B" &
intRow).Value = "Grand Total" Then
Range("B" & intRow, "G" & intRow).Select
End If
Selection.Copy
Worksheets("Sheet1").Select
Range("A" & intRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Next intRow
Sheets("Equipment List").Select
Range("A1").Select
End Sub
-----------------------------------


--
Mike Basden
------------------------------------------------------------------------
Mike Basden's Profile: http://www.excelforum.com/member.php...o&userid=31321
View this thread: http://www.excelforum.com/showthread...hreadid=510085