Copy Destination:= Code Stops Here

[Robert Christie]

Hi
The code below works perfectly on my laptop running WinXP & Excel 2002 but
fails on my PC running WinXP and Excel 2003.
I copied the spreadsheet by first burning to a CD then opening file in Excel
2003 and finally copying to my PC's hard drive.
Was that a correct or ok procedure?.

When run the code stops on each of the 3 lines containing "Copy
Destination:=Worksheet...etc.
The error message reads "Run-time error '1004' Copy method of Range class
failed".
Opening the worksheet "A-TSI - 04" the data for the first line has been
copied across.
By moving the yellow line to the 2nd line and stepping through also copys
across ok, same with the 3rd line.
Moving the yellow highlight to next line of code, will allow auto running of
code to the "End Sub" line and every thing in "A-TSI - 04" looks good.

Code from Tom Ogilvy:

Selection.AutoFilter Field:=23, Criteria1:="Y"
Set rng = Sheets("Names").AutoFilter.Range
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
rng.Copy Destination:=Worksheets("A-TSI - 04").Range("A2")

Range("D2" & Range("A500").End(xlUp).Row).Copy Destination:= _
Worksheets("A-TSI - 04").Range("B2")

Range("Q2:Q" & Range("A500").End(xlUp).Row).Copy Destination:= _
Worksheets("A-TSI - 04").Range("X2")

I've tried lowering my security level, and all else in Help.

Can anyone shed some light on this problem?.

--
Thank you

Regards

Bob C

 

[Tom Ogilvy]

I don't see anything wrong with the code. And as you say, it runs perfectly
in one version of Excel.

The symptoms you describe are typical of code placed in a sheet module which
is has incomplete references. In otherwords, if in worksheet Sheet1's code
module you had code like
worksheets("Sheet2").Select
Range("A1").Value = 3

with the intention of setting Sheet2!A1's value to 3, you would in fact set
Sheet1!A1's value to 3 since an unqualified range reference in the code
module refers to that sheet, not the active sheet if they are different.

This type of problem "goes away" when you step through the code. All that
matches most of what you say. However, this would be consistently a problem
in either version of Excel - and you claim you have just copied a working
file to another location - no changes - and it doesn't work. That doesn't
make sense to me.

Once a file is open and you are working totally within that file, security
setting shouldn't have any effect.

If I was moving a file I would copy the file to CD using Windows Explorer or
similar, move/transport to other location, copy from CD to hard drive, open
hard drive copy in Excel.

 

[Robert Christie]

Hi Tom

Thanks you for your reply.
I'm stumped.
I'll look around tomorrow for stray code perhaps rewrite the code in another
module.
I really cannot work it out.
Perfect on one computer hopeless on the next.:-(

Tom I just had a thought.
4 days ago I installed a wireless network PCI card to my laptop.
When I tested it out I pulled the file across to the laptop from my PC.
I didn't make any changes to the file just looked at it.
I tranfered the file to the laptop via a CD later, added your code and tidy
up some code documention, saved the file back to the CD for copying back to
the PC.
Could something have happened when I looked at the file?
I really don't know.

Well it's 2 in the morning nearly time for some sleep.

Thanks again Tom

Regards Aussie Bob C.

 

[Tom Ogilvy]

Best I can say is open a new workbook on the troublesome machine. copy the
pertinent data from troublesome workbook. Copy the pertinent code from the
troublesome workbook. Save this workbook and then see if the code works.

 

[Robert Christie]

I try that and send results back to this post.

Thanks again Tom