Macro change from 2007 to 2010

[VegasNath]

Hi,
Moving from Excel 2007 on Windows XP to Excel 2010 on Windows 7, I am experiencing an issue with the following code – It just stalls at the paste formats line and refuses to continue….

'Update Reconciliation
wb1.Sheets("V+ Reports").Cells.Copy
wb0.Sheets("V+ Reports").Range("A1").PasteSpecial Paste:=xlPasteValues
wb0.Sheets("V+ Reports").Range("A1").PasteSpecial Paste:=xlPasteFormats

I have tried adjusting to the following in the hope activation / sheet selection might help, but no difference.

'Update Reconciliation
wb1.Sheets("V+ Reports").Cells.Copy
wb0.Activate
wb0.Sheets("V+ Reports").Select
wb0.Sheets("V+ Reports").Range("A1").PasteSpecial Paste:=xlPasteValues
wb0.Sheets("V+ Reports").Range("A1").PasteSpecial Paste:=xlPasteFormats



Any idea’s please?

[HansV]

It shouldn't be necessary to activate the workbook and to select the sheet.

Is wb1 a .xls workbook and wb0 a .xlsx/.xlsb/.xlsm workbook, or vice versa?

[VegasNath]

Hi Hans, thought that myself but felt worth a try.... Both are xlsm.

[HansV]

Does it work better if you change

wb1.Sheets("V+ Reports").Cells.Copy

to

wb1.Sheets("V+ Reports").UsedRange.Copy

[VegasNath]

Unfortunately not.

[HansV]

There must be something else going on that has nothing to do with Excel 2007/2010. Has either of the worksheets involved been changed in another way - merging cells, or something like that?

[VegasNath]

No. I bypassed the line and had the same issue with the next set of sheets.

So i switched back to 2007 win XP and it worked fine.

Does not make any sense : (

[HansV]

Does not make any sense : (

I don't understand it either. The changes from Excel 2007 to Excel 2010 aren't large...

[Rudi]

I too am baffled at why this code is freezing?

how about another approach?

Code: Select all

    wb1.Sheets("V+ Reports").Copy Before:=wb0.Sheets(1)
    wb0.Sheets(1).UsedRange.Value = wb0.Sheets(1).UsedRange.Value