Results 1 to 10 of 10
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copy range fails in xl97 (xl97, xl2000 )

    Hi all,
    I have my usual problem with getting the same code to run in both xl97 and xl2000. Here's my latest find <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>.

    I have two named ranges on two different sheets. At one point in the code I want to copy the data pointed to by one range into the other (it updates a table on a sheet).

    This code works fine in xl2000 but fails in xl97:

    Select Case EquipList
    Case Is = 1
    Set rngSrc = Range("OptClass1")
    Case Is = 2
    Set rngSrc = Range("OptClass2")
    Case Is = 3
    Set rngSrc = Range("OptClass3")
    End Select

    Set rngDest = Range("OptEquip")
    ' next line fails in xl97, ok in xl2000
    rngSrc.Copy rngDest

    The error is on the .Copy and it says "Copy Method of Range Class Failed". I've tried other ways to write this but haven't come across the one that works. Any ideas?

    Deb <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy range fails in xl97 (xl97, xl2000 )

    I've used arrays to create a very fast copy. They only copy values though (in my quick test at home)- so I don't know if this will work for you:

    <pre>dim varArr() as variant
    varArr = rngSrc
    rngDest = varArr</pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy range fails in xl97 (xl97, xl2000 )

    Something strange here. I can not get your code to fail on my XL 97/SR2. By making the source and destination ranges different sizes, I can get a failure, but not with that message. If you use the Copy without the Destination range parameter, will it work? If so, can you then use a separate Paste method?

    You should also put a Case Else in your Select Case to insure that OptClass1 is Set to something, or you issue an error message and exit.
    Legare Coleman

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy range fails in xl97 (xl97, xl2000 )

    It worked on your xl97?? Weird, I have the same SR2. To answer your question, it didn't matter if I used "Destination" or not. The source range holds 12 strings (in one column) and the destination points to a single cell on a different worksheet which I want to update with new data based on the radio button selected. Works fine for me in xl2000.

    I ended up doing a stuid workaround but I hate it because the screen flickers quite a bit even with ..ScreenUpdating=False. I used the .PasteSpecial since I already had the range selected and .Paste doesn't work off of ranges directly but does okay on Worksheet objects. I tried changing it to work off of Worksheets('somename") but couldn't get it right.

    When I do the .PasteSpecial the destination range remains highlighted and I couldn't get rid of it without setting .CutCopyMode=False and then selecting some other cell. This is where the screen flicker comes in. Here' s the code ( I hate it):
    <pre> Select Case EquipList
    Case Is = 1
    Set rngSrc = Range("OptClass1")
    Case Is = 2
    Set rngSrc = Range("OptClass2")
    Case Is = 3
    Set rngSrc = Range("OptClass3")
    End Select

    Set rngDest = Range("OptEquip")

    If InStr(1, Application.Version, "9.") = 1 Then
    rngSrc.Copy rngDest 'fails in xl97, ok in xl2000
    Else
    rngSrc.Copy
    rngDest.PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
    Range("K1").Select
    End If
    </pre>

    Thanks for the note on the Select Case, I almost always add an Else but in this case the data comes from three radio buttons so there is no chance of something else slipping in, still it's a good idea for completeness though. I'll change it to default to radio button 1.

    Deb <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy range fails in xl97 (xl97, xl2000 )

    I ended up doing something similar (I changed it once again after my last post of using .PasteSpecial because of the screen flicker).

    Using an array works but not quite as you had it. You get an error if you define varArr as an array, and I could get it to copy the variant to the range directly (although I've used this technique for other things). I ended up doing this:

    <pre> Dim v As Variant
    Select Case EquipList
    Case Is = 1
    Set rngSrc = Range("OptClass1")
    Case Is = 2
    Set rngSrc = Range("OptClass2")
    Case Is = 3
    Set rngSrc = Range("OptClass3")
    Case Else
    Set rngSrc = Range("OptClass1")
    End Select

    Set rngDest = Range("OptEquip")
    v = rngSrc
    For k = 1 To UBound(v, 1)
    rngDest.Cells(k, 1) = v(k, 1)
    Next k


    ' fails in xl97 SR2, ok in xl2000
    ' rngSrc.Copy rngDest
    </pre>

    It works and doesn't flicker the screen like my first "fix" but it always irks me when I find yet another undocumented difference in VBA between xl97 and xl2000 (I have seen about 12 so far).

    Thnx, Deb <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy range fails in xl97 (xl97, xl2000 )

    Is there any chance you could edit the workbook down to just what fails and post a copy here so we could play with it?
    Legare Coleman

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy range fails in xl97 (xl97, xl2000 )

    I got it working now, just had to manually copy cells (using For loop to copy between ranges) from one range to another and not use the .Copy command. The workbook is used to generate some bill of materials for my company off of a pre-defined set of options and there's lots of pricing info and other proprietary stuff so I'd have to dust off my resume if I uploaded it (and find a good lawyer like the one Condit has) <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Your ideas got me to a solution.
    Thnx, Deb <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy range fails in xl97 (xl97, xl2000 )

    OK, now I have Excel 97 to check out- that code worked in Excel 2000 but not in 97. My apologies- but you do have an answer now.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    New Lounger
    Join Date
    Mar 2001
    Location
    Blue Mountains, New South Wales, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy range fails in xl97 (xl97, xl2000 )

    <P ID="edit" class=small>Edited by gwhitfield on 27-Aug-01 15:19.</P>Hyperlinks added
    Deb,

    You've got me thinking as I've seen an error similar to that myself. Anyway, I tried something similar to what you did, and got the same error. I'm using XL97 SR-2 (k). After some hacking of the code I found the error is in selecting the destination range.

    I found the following code fixed the problem....

    rngsrc.Copy
    rngdest.Parent.Activate
    rngdest.Select
    rngdest.Parent.Paste

    BUT GET THIS....once I ran the above code, and then tried to recreate the error by using a single line

    rngsrc.copy rngdest

    I COULDN'T.....In other words, it then worked. I was trying to get the error again to see what the MS knowledge base had to say. It does say something, (<A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q108/4/73.asp>http://support.microsoft.com/support/kb/ar...s/Q108/4/73.asp</A>) as to why it shouldn't work, but of course, not why it then should

    ...go figure...

    Have fun...

  10. #10
    New Lounger
    Join Date
    Mar 2001
    Location
    Blue Mountains, New South Wales, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy range fails in xl97 (xl97, xl2000 )

    Deb,

    I replied to your post on the above item, but realised later I'd given you a bit of a bum steer. I think the problem is that you might be running the code off the option button click event, which is on the worksheet. When you click the activex control, it takes focus away from the sheet. It then can't find the destination sheet.

    You can fix this by either selecting a sheet or range as the first line of code on the click event (rngsrc.parent.activate, or similar), or using one of the forms buttons (the ones carried over from XL95), which don't act like an activex control. I'm sure you're not going to be keen to use one of the forms buttons though, given some of the other XL97 grief you've had.

    I've never used XL2000, so can't comment on why it works there, but maybe the activex controls now default to not take focus when placed on a sheet?

    see ya.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •