Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range().Copy method freezing Excel (XP)

    Hi, I am developing a customised spreadsheet, with VBA running various calculations, filters etc.

    One sub is handling copying various information from the main sheet to specific other sheets, and for this I am trying to use:

    sheets(1).range("A2:m2").copy sheets(2).range("A2:m2")

    this works fine in a blank workbook, but is throwing errors in the workbook i am developing.

    I have already completely rebuilt the workbook from scratch as I thought it may be corrupted, but the same error occurs.

    If you try this operation in the debug window of the attahced sheet then it throws an automation error.

    Any ideas? I was using sheets(2).range() = sheets(1).range and that was working fine, but i want to copy to preserve the formatting.
    Attached Files Attached Files
    Thanks,

    pmatz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range().Copy method freezing Excel (XP)

    The line of code executes without errors when I try it, as does the Temp procedure.

    Perhaps something else is interfering, e.g. an add-in. Try Jan Karel Pieterse's <!post=Systematic Approach to Behavioral Problems in XL,290455>Systematic Approach to Behavioral Problems in XL<!/post>.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range().Copy method freezing Excel (XP)

    No way?! The code is fine when you execute it in the attached file?

    I thought it might be another sub getting in the way.

    Looking at said link now.

    Cheers
    Thanks,

    pmatz

  4. #4
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range().Copy method freezing Excel (XP)

    Actually, it cant be other code getting in the way, because after the error, i can close the spreadsheet (although cant select any cells in it beforehand) and close the vb editor, left with the excel app. but cant close this, only by using task manager end process.
    Perhaps its adobe acrobat addin, will check.
    Thanks,

    pmatz

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range().Copy method freezing Excel (XP)

    Oh wait, the code runs fine if any sheet except the second one is active.
    If I run it while the second sheet is active, I get a Type Mismatch error in updateSS (called from the Worksheet_Change event procedure).
    The line

    Set r1 = Range("A2")

    refers to cell A2 on the active sheet, not to cell A2 on the sheet that calls the Worksheet_Change event procedure.

  6. #6
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range().Copy method freezing Excel (XP)

    ah, thats ok though, i can sort that, at least its an error that stops the code and not one that freezes excel.exe.
    I get this, and then excel will not close / function
    Attached Images Attached Images
    Thanks,

    pmatz

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range().Copy method freezing Excel (XP)

    Sorry, I have no idea why you get an Automation error - I don't get that.

  8. #8
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range().Copy method freezing Excel (XP)

    no worries- must be something to do with my excel.
    what would a best workaround be? as i said, i can use r1 = r2 to set the values from one sheet to another, but i really want to capture the formats (all formatting done on sheet1 via code)
    Thanks,

    pmatz

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range().Copy method freezing Excel (XP)

    I'd check Pieterse's troubleshooting guide to see if you can find the culprit.

    As an interim measure: what happens if you temporarily comment out the call to updateSS in the Worksheet_Change event procedures?

  10. #10
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range().Copy method freezing Excel (XP)

    Hmmm

    Gon ethrough the start up issues / trouble shooting

    Have disable all addins, xlas, adobe toolbar etc, plain excel,

    commented out all other code apart from temp

    when i run it, get error, this time the error below.

    i think excel itself must be corrupted. But when i open a new workbook, i can use this code fine. Going to have to find workaround i think - this needs to be completed by Friday . Will just try it on another machine first.
    Attached Images Attached Images
    Thanks,

    pmatz

  11. #11
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range().Copy method freezing Excel (XP)

    Well, I get the same error from this sheet opening on a different machine also.

    Are you SURE it works on yours? Excel 2002 SP3

    The .Copy method works if the sheet is the same, but as soon as i try and set the destination to a seperatesheet, it trips up/
    Thanks,

    pmatz

  12. #12
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range().Copy method freezing Excel (XP)

    In fact, the .copy method works fine to copy from any other sheet across sheets, just when copying from sheet 1 it doesnt.

    So i manually copied sheet 1 range (A1:m34) and pasted into a new workbook. and saved the new work book.
    then closed and reopened excel, with the new workbook, went into vba and tried to do the .copy method from sheet1 - same error <img src=/S/mad.gif border=0 alt=mad width=15 height=15>
    Thanks,

    pmatz

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range().Copy method freezing Excel (XP)

    I'm using Excel 2003 SP3 at the moment. I will try on Excel 2002 SP3 later today.

  14. #14
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range().Copy method freezing Excel (XP)

    Thanks Hans - Im going to leave it now 4 a bit, i almost hope (!) you get the same issue on Excel 2002 <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Thanks,

    pmatz

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range().Copy method freezing Excel (XP)

    Yes, I do get the same error as you in Excel 2002 SP3, and only with the first sheet. No idea why yet. If I find something, I'll let you know.

Page 1 of 2 12 LastLast

Posting Permissions

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