Excel Freezing during Filling Cells

[chicosbailbonds]

Does anyone have a solution? I didn't change any code recently but this started to happen yesterday. My Macro goes about copying a number of tabs out of a master Spreadsheet and then pastes them into a new Spreadsheet and pastes special a lot of data using defined names. I would appreciate any help as this is something I need to run daily.

Thanks,
Jim

 

[ChrisM]

You haven't given much info to go on. Is it permanently freezing? What specific line of code is causing the issue?

FYI, I've always used an Excel 2003 paste-special macro keyboard shortcut I made, and when I moved to Excel 2007, the macro took a loooong time with any significant amount of cells selected. So much so that I don't really use it any more unless it's no more than 20 or 30 cells.

 

[chicosbailbonds]

It seems to freeze permanently, I'm still using 2003 XL no updates as far as I can tell. I'm somewhat new to VBA but have just enough knowledge to be dangerous. I can see what sheet it freezes on and in the bottom left corner of Excel I see filling Cells and it is about 90-95% green with just a bit that is grey. I don't know how to test exactly what line is causing the issue. to paste special I have used Sheets("Sheetname").Range("DefinedName").Value = Sheets("Sheetname").Range("DefinedName").Value, I use this many times across many sheets. I appreciate the help.

 

[ChrisM]

So you're really not using "Paste Special", you're just using VBA to set one cell's value equal to another?

Let's try to use VBA to Copy and Paste Special instead, see if that works:

Sheets("Sheetname1").Range("DefinedName").Copy
Sheets("Sheetname2").Range("DefinedName").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[chicosbailbonds]

Sorry Took me a while to update all of them, this seemed to run slower but still froze on filling sheets.

 

[Datsmart]

I would suggest you put some error trapping in your code and see which worksheet/DefinedName is causing the error.
Then check what is in that sheet/name that errors when copied.

Did you put anything new into the workbook since the last time it worked correctly?
That might be your problem.

 

[chicosbailbonds]

John I agree some error checking is needed but I'm not too sure of how to do that

 

[Datsmart]

If you would post your code I will see if I can give you a hand with that.

Follow the suggestions here to use code tags:
http://www.mrexcel.com/forum/misc.php?do=bbcode#code