Newbie - Help with a macro

[Mike Basden]

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

 

[Guest]

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

 

[Mike Basden]

Fantastic! Thanks Ben.

(Now if only my DeterminUsedRange would work...)

Cheers,
Mike

 

[Mike Basden]

(Now if only my DeterminUsedRange would work...)

Fixed...Thanks.

 

[Guest]

Just a couple of comments on things I noticed this morning. I just now
checked in to see if anyone else had pointed them out, but apparently not.
When you dimension multiple variables on the same line the way you do, only
the one that says as "whatever" gets dimensioned to that type. It appears
that you meant to dimension those other variables to int, so you would change
the line to:

Dim intRow as integer, intCol as integer, RowCount As Integer

Otherwise intRow and IntCol would both be of type variant. Also on the line:

For intRow = 19 To RowCount Step 1

you actually want to change it to Step -1