What did I do? (Select Method of Range Class Failed )

[HotRod]

I've been running some code successfully for a few weeks now, the only thing
that I've changed is that I now copy a range of data from the Main Sheet to
a Working Sheet before I start manipulating the data. I haven't change the
actual code below that now gives me the ERROR "Selection Method of Range
class Failed"


Set Rng = Worksheets(SheetName2).UsedRange.Rows

'I Get the Error trying to execute the next line
Application.Worksheets(SheetName2).Range("A" & First_Row, "G" &
Rng.Rows.Count).Select

 

[Guest]

Is the sheet visible at the time you go to make the selection?

Jim Thomlinson

 

[HotRod]

Yes this solved the problem I didn't realize I couldn't select cells if the
page didn't have the focus.

 

[Guest]

That is why I always try to avoid selecting. It requires a lot more code and
a lot more effort. I am not big on effort.

Jim Thomlinson

 

[HotRod]

Jim
I guess that's why we use code to begin with, none of us are big on
effort.

I'm currently developing a system that matches peoples names to determine if
they are the same name but spelt different. In a test of 23000+ names
it now runs at a 99.8% accaracy. splitting the number of "UNIQUE' visitors
to our centres into each quarter. Trust me this will save me a ton of work,
of making sure that Mike Smith and Micheal Smith are the same person.

 

[Guest]

From watching your posts you are getting mighty good at this. For you the
biggest step forward that you could make at this point would be to make
greater use of worksheet, borkbook and range objects. Also name your sheets
in code and refer to them by their code name. For example use Sheet1.select
instead of
Sheets.("Sheet1").select

Jim Thomlinson

 

[HotRod]

Jim
Just a question since this really has been what I'm wondering

1) If I use Sheet1 instead of Worksheet("SheetName") are all of the sheets
labeled sequentialy from Sheet1 - Sheet100, and what happens if someone
changes the order of the worksheets?

2) I've now replaced the code
Application.Worksheet("SheetName").Range() etc.

with

Rng.Range() etc.

In the code below would it make more sense to create to Range Objects?

'COPY MAINSHEET TO
WORKSHEET --------------------------------------------------------------------------------------------------
'Select Range on first Sheet and then copy Values only.

Set Rng = Worksheets(SheetName).UsedRange.Rows

'Clear old sheet first
Application.Worksheets(SheetName2).Range("A" & WorkSheet_First_Row,
"Z55500").Value = ""

'COPY DATA
Application.Worksheets(SheetName2).Range("A2", "G" & (Rng.Rows.Count -
(First_Row - WorkSheet_First_Row))).Value = _
Application.Worksheets(SheetName).Range("A" & First_Row, "G" &
Rng.Rows.Count).Value

 

[HotRod]

THANKS For all of your help.

 

[HotRod]

What would you recommend instead of doing the following?

SheetName2 = "WorkSheet"

'Set Focus to WorkSheet
Application.Worksheets(SheetName2).Select

 

[Nick]

Hi Rod

The best approach, in my opinion, when referencing a worksheet object is to
use the object name. This is Sheet1, Sheet2 etc for sheets and is created
when the sheet is added. You can change these in the VB editor in the
properties window. These names can only be changed here and it doesn't
matter what the sheet name is.

For example

The first worksheet created will have the object name Sheet1 regardless of
where it is in the actual order. It may have a worksheet name (shown on the
tab) of Data. I usually rename (using the name property of the object) this
sheet wsData and then reference the object directly. e.g.
wsData Range("a1").copy

Hope this helps.
Nick