Macro Select Issue

[MikeG]

I have a macro that I activate in Sheet1.

It includes the following:

FR=100
FC=2
Sheets("Quotes Database").Cells(FR, FC).Select

My intention is simply to jump to the other worksheet (Quotes Database) and go to the specified cell. However, the macro causes an error - Runtime Error 1004 Slect method of Range class failed.

Can anyone help?

Thanks,

MikeG

 

[shg]

You can only use Select on the active sheet.

Application.GoTo Worksheets("Quotes Database").Range("B100")

 

[MikeG]

You can only use Select on the active sheet.

Application.GoTo Worksheets("Quotes Database").Range("B100")

Thanks shg.

The only problem is I want to make the reference dynamic. In other words it won't always be B100 - the values of FC and FR could change.

Actually, maybe you or someone can help me further:

In the current worksheet, I have a range named "Parts" that covers cells B10 to B49.

What I want to do is to copy this range into the "Quotes Database", starting at the cell defined by FC and FR.

So what I was thinking to do is:

Range("Parts").copy

then go to the other worksheet (that is why I needed help)

then do a

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

But is there a better way that does not even need copy.

Originally, I tried


Sheets("Quotes Database").Range(Cells(FR, FC), Cells(FR+39, FC)) = Range("Parts")

but I guess this has the same referencing problem.

Thanks,

MikeG

 

[shg]

There are several ways to do that. Here's one:

Range("Parts").Copy
Worksheets("Quotes Database").Cells(FR, FC).PasteSpecial Paste:=xlPasteValues

In general, it is almost never necessary to select anything to operate on it, and faster not to.

EDIT: Another way:

    With Range("Parts")
        Worksheets("Quotes Database").Cells(FR, FC).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

 

[MikeG]

There are several ways to do that. Here's one:



In general, it is almost never necessary to select anything to operate on it, and faster not to.

EDIT: Another way:

    With Range("Parts")
        Worksheets("Quotes Database").Cells(FR, FC).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

Thanks shg, that's perfect!

Sub Macro5()
'
' Macro5 Macro

Range("AG10:AG49").Select
Selection.Copy
Sheets("Quotes Database").Select
Cells(1, 4).Select

End Sub

and here is seems like you can Select in another sheet. Was my original problem because it was a Private macro? Am I misunderstanding?

MikeG

 

[mikerickson]

You need to activate the sheet, before selecting a cell on that sheet. As has been noted, that is seldom nessesary.

With Sheets("Quotes Database").Cells(FR, FC)
    .Parent.Activate
    .Select
End With

 

[MikeG]

You need to activate the sheet, before selecting a cell on that sheet. As has been noted, that is seldom nessesary.

With Sheets("Quotes Database").Cells(FR, FC)
    .Parent.Activate
    .Select
End With

Thanks.

MikeG

 

[MikeG]

You need to activate the sheet, before selecting a cell on that sheet. As has been noted, that is seldom nessesary.

With Sheets("Quotes Database").Cells(FR, FC)
    .Parent.Activate
    .Select
End With

Actually, I still don't think I understand. In this macro that Excel created in the Record Macro function, where does the second sheet get selected. Yet the macro works.


Sub Macro5()
'
' Macro5 Macro
'

'
Range("AG10:AG49").Select
Selection.Copy
Sheets("Quotes Database").Select
Cells(1, 4).Select

Thanks again.

MikeG

 

[shg]

In your recorded macro, the Copy applies to the selection on the active worksheet, then the next line selects a different sheet.

 

[MikeG]

In your recorded macro, the Copy applies to the selection on the active worksheet, then the next line selects a different sheet.

Thanks, and sorry for going on, but what confuses me is that the Excel recorded macro runs fine.

But if I simple paste it into a macro I previously created attached to a button, the macro trips up on Cells(1, 4).Select

Private Sub CommandButton1_Click()
Range("AG10:AG49").Select
Selection.Copy
Sheets("Quotes Database").Select
Cells(1, 4).Select

Is it something to do with the "Private" status?

MikeG