VBA -- Random Stopping

[katk]

Hi,

I'm working on a code that has a lot of parts. I tested each part individually as I created it to make sure it worked, and now I've combined them and I'm trying to run it through to troubleshoot/make sure everything works right.

The first time I ran it through, I was using Excel 2010 on Windows computer (the code was created in Excel 2011). That time, there were errors, but VBA was working fine. Now I'm back on my MacBook Pro (where I created the code) and VBA is simply completing the first two or three steps and then stopping -- no error message, no freezing, no hesitation, just returning to normal Excel functioning.

I know some people have problems with VBA stopping if they open a new program, but I haven't been touching anything while my code runs. Safari has been minimized, but it's always worked before with Safari open.

This is the beginning of the code -- it either stops after the first paste (A:D in cell A2...) or the second, depending. I should also note that I've tried many different variations of the copy/paste bit -- with selects, without selects, activating sheets, using destination instead of paste, etc.

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.

If anyone can offer me any thoughts as to why this might be happening or what in my code might be triggering this, I would be very grateful. Thanks for any and all help offered -- I really do appreciate all the assistance this forum has given me.

Katherine

 

[katk]

Update: Actually, it -always- stops after the first copy/paste. (No matter how that copy/paste action is syntaxed.) Whether I run it from Excel proper or from the VB Editor, it only gets that far. However, if I run it -again,- after the first time, in the VB Editor, I can get past the second paste (although of course at this point the information is wrong because it's performed the first few actions twice). Running it a third time does nothing.

This is driving me up the wall -- I tried breaking up my code, recording parts of it, testing it on dummy workbooks, etc. -- and it just refuses to go past that first paste. I put a msgbox after the paste and it doesn't come up (unless I'm running it a second time from VB Editor). If anyone can shed light on this, I'd be thrilled.

 

[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.

 

[katk]

I realize I'm talking to myself at this point, but just in case anyone ever does look at this, hopefully this is useful: I thought it was the new workbook that fixed the problem, but actually the problem was a faulty UDF. I removed the UDF from the workbook (or rather, had forgotten to transfer it), and now the code runs fine.