Cannot activate worksheet using VBA. "Subscript Out of Range" error.

[Cloudfall]

I have written the following test program which does not work. Does
anyone know why it doesn't? I want to copy a column from one workbook
and paste it into another. I am now getting desperate as this is
holding up work. Here is the code:

Sub CopyPaste()
Workbooks("Automation Test.xls").Activate
Worksheets("Sheet1").Activate
Range("E2:E65000").Select
Selection.Copy
Workbooks("ABNLookup.xls").Activate
Worksheets("ABNLookup").Activate 'THIS IS WHERE IT CRASHES
Range("A4").Select
End Sub

When I try to execute the line 'Worksheets("ABNLookup").Activate' it
crashes with 'Runtime Error 9 Subscript out of range'. Please help.

 

[Roger Govier]

Hi Sydney

It is the Sheet you are telling it to Activate, not the WB name
Worksheets("Sheet1").Activate
assuming the sheet is named Sheet1 in the second workbook

 

[Roger Govier]

Hi Sydney

Hit the send button too soon.
Meant to also say that you could achieve this without all the Selection
and activation if you use the following approach

Sub CopyPaste()
Dim source As Range, dest As Range
Set source = Workbooks("Automation
Test.xls").Sheets("Sheet1").Range("E2:E65000")
Set dest = Workbooks("ABNLookup.xls").Sheets("Sheet1").Range("A4")
source.Copy dest

End Sub

 

[Bob Phillips]

Runtime error 9 usually means that you are trying to reference a
non-existent object. You need to check what is the actual name of the
worksheet in the second workbook.

BTW, if just doing a strsight copy, that is not formulae or values, you can
do it directly, like so


Workbooks("Automation
Test.xls").Worksheets("Sheet1").Range("E2:E6500").Copy _
Workbooks("ABNLookup.xls").Worksheets("Sheet1").Range("A4")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Michael Bednarek]

I have written the following test program which does not work. Does
anyone know why it doesn't? I want to copy a column from one workbook
and paste it into another. I am now getting desperate as this is
holding up work. Here is the code:

Sub CopyPaste()
Workbooks("Automation Test.xls").Activate
Worksheets("Sheet1").Activate
Range("E2:E65000").Select
Selection.Copy
Workbooks("ABNLookup.xls").Activate
Worksheets("ABNLookup").Activate 'THIS IS WHERE IT CRASHES
Range("A4").Select
End Sub

When I try to execute the line 'Worksheets("ABNLookup").Activate' it
crashes with 'Runtime Error 9 Subscript out of range'. Please help.

It means there is no such worksheet ("ABNLookup") in the workbook
"ABNLookup.xls".

There is no need to Activate and Select; just copy the data:

Workbooks("Automation Test.xls").Worksheets("Sheet1").Range("E2:E65000").Copy Destination:=Workbooks("ABNLookup.xls").Worksheets("ABNLookup").Range("A4")

However, I would advise to be more specific with the source range.
For one, future versions of Excel will allow more than 2**16 rows.
Try this:

Function LastCell(rngStartCell As Range) As Range
With rngStartCell
Set LastCell = .Parent.Cells(.Parent.Rows.Count, .Column).End(xlUp)
If Len(LastCell.Value) = 0 Then Set LastCell = rngStartCell
End With
End Function

and then:

With Workbooks("Automation Test.xls").Worksheets("Sheet1")
.Range("E2", LastCell(.Range("E2"))).Copy Destination:=Workbooks("ABNLookup.xls").Worksheets("ABNLookup").Range("A4")
End With

But first, you have to make sure that the worksheet "ABNLookup" exists
in the workbook "ABNLookup.xls".

 

[Ken Macksey]

Hi

It is also possible that you accidentally added a space before or after the
name when naming the worksheet. The actual name may be " ABNLookup" or
"ABNLookup "


Ken

 

[Cloudfall]

Gentlemen (Roger Govier, Bob Phillips, Michael Bednarek, Ken Macksey),

This project got so urgent they brought in another VBA programmer (the
third!) to try to work out why this program used to work and now
doesn't. The mystery has been solved. By the way, I always try to give
feedback to those kind souls who try to help me out with my programming
problems. I know you don't always get feedback, but you have to
understand that often there is an emergency in progress, someone posts
the problem, a kind soul solves that problem, and the next few days are
spent frantically implementing the solution and putting out fires.
However, I usually have the time at some point to get back and thank
those who have tried to come to my rescue.

The VBA programmer they brought in (Georgina) found what the problem
was. I have learnt something from all this (well I suppose you would,
wouldn't you, after three people spent three days trying to find out
why a working program wasn't working). But firstly:
1. Roger, Worksheets("ABNLookup").Activate was correct (not "Sheet1").
2. Roger, I have in the last three days become aware of the technique
of not using Selection and Activation and plan on implementing your
recommendations. However, I am neither a good nor confident VBA
programmer yet. Breaking down a programme into its elemental steps
allows me to more easily debug it. In this case here, when I finally
realised that the problem was not being able to activate another
workbook from my programme, it allowed the new programmer to have the
insight into what lay at the core of the problem.
3. Bob, thank you for the recommendation regarding a better way of
implementing my programming intentions. Please see point two above.
4. Michael, wow. I don't have the time at the moment to try to get my
head around your recommendation, but, yeah, I'm getting a little tired
of amateurishly selecting "E2:E65000" as my source range.
5. Ken, yes I have done what you suspected in the past but not in this
case. The worksheet name was correct.

OK. So, what really did Georgina work out to be the problem? Why did
the program work in the past and why isn't it working now? Well, in
developing the programme I had modules everywhere, code everywhere, and
so I recently decided on a little housekeeping. I put all the code into
"ThisWorkbook". And I didn't test to see if it still worked! Guess
what. You can't activate other workbooks if your code is in
"ThisWorkbook". I have never read this anywhere. No one has ever told
me this. Georgina didn't know this. It came to her in a "Eureka"
moment. I have moved the code into a module and it works.

Thank you for all your help. You have given me ideas which will keep me
busy for a while. Incidentally, and this is pure idle curiosity, when
opening the spreadsheet with all of the VBA associated with this
application (of which, I suppose, there is quite a bit), why does it
take almost exactly two minutes to get to the "Enable macros" screen
and a further two and a half minutes after that to open?

Anyway, I wish you all the best and again, thank you.

Regards,

Cloudfall.

 

[Michael Bednarek]

Gentlemen (Roger Govier, Bob Phillips, Michael Bednarek, Ken Macksey), [snip]
By the way, I always try to give
feedback to those kind souls who try to help me out with my programming
problems.

[snip]

Thanks for the feedback; very much appreciated.

so I recently decided on a little housekeeping

Self-inflicted wounds often hurt the most.

[snip]

Incidentally, and this is pure idle curiosity, when
opening the spreadsheet with all of the VBA associated with this
application (of which, I suppose, there is quite a bit), why does it
take almost exactly two minutes to get to the "Enable macros" screen
and a further two and a half minutes after that to open?

How large is the file? Where is it? On a Network? What are the network
specifications? Wire speed? OS on the server? Server specs? Workstation
specs? What else is running on the server? What else is running on the
workstation? Which anti-virus program is running on the workstation?
Which anti-virus program is running on the server?

 

[Roger Govier]

Hi

(I'm not sure how I should address you, I always thought it polite to
use forename, but I note you have signed yourself Cloudfall on this
occasion. Perhaps that is how you prefer to be addressed, anyway, I
meant no offence by addressing you as Sydney previously.)

Thank you for the detailed response telling us all of your eventual
solution to the problem. I am sure I speak for others in appreciating
both your gratitude and explanation. Whilst I may not have been tempted
to put all code in the This Workbook module, I too was totally unaware
of this outcome.

I also do not regard myself as a good or confident VBA programmer, and
the suggestion I made was only as a result of what I have learnt from
these newsgroups. However, I have noticed a marked increase in speed of
execution of my programmes since adopting this approach.
At first the logic of - source.copy destination - did not sit
comfortably with me without doing all of the selecting and activating
first, but having noted the methodology used successfully by others, I
began using it and have been delighted with the results.

With regard to the slow opening of your files, I experienced something
similar recently. In my case, I tracked it down to a "bloated" keyboard
file which had grown to 670MB. I closed Excel, deleted it and opened
Excel again, and it recreated the file at 11MB in size.
Of course, I needed to re-customise the bar and I have now followed the
advice given in other postings and copied my customised bar to another
location, and should the problem re-emerge, I can just move the copy
file back in. In my case the file was located in
C:\Documents and Settings\Roger Govier\Application Data\Microsoft\Excel
but a search for *.xlb will find the location on your machine.

This may of course have no bearing on your problem, but its worth a try.

 

[Cloudfall]

Hi Michael,

1. I will test, test, test any changes I make in future and make no
assumptions. The time I wasted on this problem was huge.

2. The Excel files with VBA in them are about 18 MegaBytes. Small 200
KB data files (no code) take about a second to open. I think it is the
size of the files that is making them take over 4 minutes to open.

3. I'm trying to get your Function "LastCell" working. I wrote the
following program to try and call LastCell:

Sub test()
Dim rngStartCell As Range
Set rngStartCell = LastCell("E2")
Range(rngStartCell).Select
End Sub

I simply want to select the first empty cell at the end of a column of
data. I don't understand "Ranges" very well (VBA told me "E2" was a
type mismatch). Can you please help?

4. I will also in future be using far fewer "activates" and "selects"
in the final code (but I will use them in early development) as I
suspect they seriously increase the programme's execution time.

Regards,

Terry.

 

[Cloudfall]

Hi Roger,

Thank you for your reply.

(I actually put Sydney in my email address to stand for Sydney,
Australia, which is where I live. My name is Terry. I am a frequent
user of microsoft.public.excel when I am developing an application,
which is not that often, and I get replies to my questions from a lot
of people. Although I've never asked, I've always wondered where these
people lived. Sydney? Australia? USA? GB? New Zealand? Canada? I
thought that by putting Sydney in my email address it might give others
an idea of where I'm from. In researching names for my youngest son who
was born 15 years ago my wife and I read through three books of names
for a couple of weeks. This is where I came across the name Cloudfall.
By the way, we wound up calling our youngest "John". Cloudfall was the
name of a mythological white horse. I used to have a brown wooden
sailing boat that a friend and I raced on Sydney Harbour about 20 years
ago. When I saw the name Cloudfall, I thought it would make a
wonderfull name for a white sailing boat because a white spinnaker
running before the wind looks like a cloud. However, I've scratched
my sailing itch and if I ever get nostalgic about sailing I can
accurately recreate the experience at home by standing under a cold
shower while tearing up hundred dollar bills. Watches and sunglasses
overboard, broken centreboards and masts, rigging breaking every week.
Thoroughly scratched. But I liked the name Cloudfall so much that
I've used it as an alias in my internet dealings. So that's my
story. I try to stay relatively anonymous in my internet activities as
I just don't want someone typing in my name and coming up with all
sorts of personal information about me, such as the information I've
just divulged here. You've certainly not caused me any offence by
addressing me as Sydney. I really don't mind how you address me as I
really have no preferences.)

In relation to not using "activates" and "selects", I have been
trying to work out how to get around using them for some time now. The
problem is, I only get to develop applications when there is repetitive
work that is taking up an inordinate amount of someone's time. I've
developed the latest application and there aren't any more to be
done. So, no more VBA programming. I've been given a database job
(creating reports).

I did a search for the *.xlb file. Mine is 8 KB, so it isn't the
problem. My VBA Excel files are 15 Mbyte or larger. Excel opens non-VBA
data files of 200 KB size in about a second, so I think it is the size
of the VBA files that is the problem.

Anyway, it has been very pleasant and helpful discussing these matters
with you and the others. Have a long and pleasant life.

Regards,

Terry.

 

[Michael Bednarek]

On 9 Feb 2006 20:30:09 -0800, Cloudfall wrote in
microsoft.public.excel:

[snip]

3. I'm trying to get your Function "LastCell" working. I wrote the
following program to try and call LastCell:

Sub test()
Dim rngStartCell As Range
Set rngStartCell = LastCell("E2")
Range(rngStartCell).Select
End Sub

Re-read my example closely. My function expects a Range as its
argument - "E2" is a string. To pass it as a Range, use Range("E2"):
Set rngStartCell = LastCell(Range("E2"))
or more generally:
Set rngStartCell = LastCell(Workbooks(varIdxWB).Worksheets(varIdxWS).Range("E2"))

 

[Bob Phillips]

With regard to the slow opening of your files, I experienced something
similar recently. In my case, I tracked it down to a "bloated" keyboard
file which had grown to 670MB. I closed Excel, deleted it and opened
Excel again, and it recreated the file at 11MB in size.
Of course, I needed to re-customise the bar and I have now followed the
advice given in other postings and copied my customised bar to another
location, and should the problem re-emerge, I can just move the copy
file back in. In my case the file was located in
C:\Documents and Settings\Roger Govier\Application Data\Microsoft\Excel
but a search for *.xlb will find the location on your machine.

Another way is to build it dynamically, say in Personal.xls. This is what I
do, I like the control <vbg>

 

[Bob Phillips]

Terry,

You might want to check the temporary files, these can sometimes slow things
down. They don't always get properly cleared out. You can find the temp
directory by doing

Windows Start button: Start>Run>%temp%>OK

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Thanks for that feedback, it is the best we have ever received I think.

As to the problem, that doesn't sit easily with me, so I think I will have
to do some testing to satisfy my own mind.

Regards

Bob

 

[Roger Govier]

That's a good thought Bob, as I have noticed it creeping up in size
again.
When I get a spare moment I shall see if I can figure out how to do this
successfully.