Compatibility and VBA dropping out

[katk]

Hi,

I posted a problem on the MrExcel forum this morning. I haven't gotten any replies, and although I don't mean to cross-post, I thought it might be worthwhile to attack the problem from a different angle.

Basically the issue is that I built a number of Sub procedures using Excel 2011 on a MacBook Pro, and then combined them into one large procedure. The computers that will be running this in the future are PCs, however, so I initially tested the code out on a PC (Excel 2007 I think). It had a number of errors but was running through properly otherwise. However, in trying to fix the errors on my Mac, I found that after the third step of the Sub, VBA simply "dropped out," returning excel to normal functionality with no error message, no freeze, no hesitation, nothing. The step is drops out is a simple copy/paste, as is the next step, but it simply "hangs up" (as I saw another user say) in between them. I've tried changing the syntax of both statements but to no avail.

So my question is: are there known instances of this happening, where either Excel 2011 VBA just drops out or does so after a code is used on different versions of excel/computers or anything like that? It could be my coding, but the code works fine on the PC and it's such an innocuous line that I'd be surprised if it were a direct result of the statement it drops out on. Also, if I run the code from VB Editor, and then run it again, I can get 2 lines farther -- but no more. If this is a system glitch rather than a coding glitch, it'd be great to know so I don't keep tearing my hair out trying to fix my code. (Sidenote: I did try restarting my computer.) Since this is a VBA-specific forum, I thought there might be a better knowledge of VBA's quirks that could help me figure out this baffling problem.

The relevant section of code and more detail are available in the mrexcel posting (mrexcel.com/forum/showthread.php?t=568372). Any help on this -- even just anecdotes of similar issues -- would be greatly appreciated. Thank you so much!

[Aussiebear]

Sub PostOffs()
'SaveAs with date
ActiveWorkbook.SaveAs Filename:= _
"Office:Katherine's Documents:Post Offs:PostOffs Detroit" & Date & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
' DeletePrices Macro ' Delete columns C-D, G-O in Prices
Sheets("Prices").Activate
Columns("C:D").Delete Shift:=xlToLeft
Columns("G:O").Delete Shift:=xlToLeft
' MovePrices Macro
' Copy paste prices to price setup
Sheets("Prices").Range("A1:D2000").Copy Destination:=Sheets("Price Setup").Range("A2")
' It usually gets at least this far
Sheets("Prices").Range("E1:F2000").Copy Destination:=Sheets("Price Setup").Range("G2")
Range("E2:F2").Select
Selection.AutoFill Destination:=Range("E2:F2000")
Range("I2:V2").Select
Selection.AutoFill Destination:=Range("I2:V2000")
'It never gets this far
Cells.Select
ActiveWorkbook.Worksheets("Price Setup").Sort.SortFields.Add _
Key:=Range("A1:A2692"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Price Setup").Sort.SortFields.Add _
Key:=Range("F1:F2692"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Price Setup").Sort
.SetRange Range("A1:X2692")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With 'then a lot more code, etc.,
End Sub

[katk]

Okay, I think I've fixed the problem, although I don't know what the problem was... but if anyone looking at this has the same problem, I think for me the workbook was broken or damaged in some way. I pasted all the information I had, plus my codes, to a new workbook and it started to function again.