Thread: Newbie - Help with a macro

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
-----------------------------------

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
>
>

Fantastic! Thanks Ben.

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

Cheers,
Mike

Originally Posted by Mike Basden

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

Fixed...Thanks.

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

--
Kevin Vaughn


"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
>
>