Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Destination:= Code Stops Here
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:D" & 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Destination:= Code Stops Here
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. -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... 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:D" & 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Destination:= Code Stops Here
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" wrote: 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. -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... 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:D" & 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Destination:= Code Stops Here
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. -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... 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:D" & 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code Stops Running Instead of Debugging | Excel Discussion (Misc queries) | |||
Instead of debugging, code just stops running | Excel Discussion (Misc queries) | |||
can someone make code for tab/enter stops for me please? | Excel Discussion (Misc queries) | |||
(trying again...) code stops executing | Excel Programming | |||
VBA code stops executing | Excel Programming |