Range().Copy method freezing Excel (XP)

Topic

New Reply

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.

Reply | Quote

Replies

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 Systematic Approach to Behavioral Problems in XL.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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/

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thanks Hans – Im going to leave it now 4 a bit, i almost hope (!) you get the same issue on Excel 2002

Reply | Quote

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.

Reply | Quote

I have found several reports of the same error while copying/pasting to a different worksheet in newsgroups/discussion forums, unfortunately without an explanation of the cause or a solution. This variant of the problem apparently occurs in Excel 2002 only, not in Excel 2000 or Excel 2003.
It’s a mystery to me why the problem only occurs with the AEL 2008 sheet.

(BTW, the workbook uses the new Calibri font – was it originally created in Excel 2007?)

Reply | Quote

well, thats quite a relief in a way! Thanks for taking the time Hans

I will find a way around it I’m sure, start again tomo though.

I really like the calibri font I have to admit, it has become my new ‘favourite’ replacing good old tahoma.

I have Visio 2007 installed here, rest of office is XP2002, apart from Outlook which is 2003 . Calibri came in with Visio, I would use it for email but no one else would appreciate it as yet as the Visio 2007 is trial specific to me!

Have a good evening

Reply | Quote