Macro randomly stopped working? I have used this macro for months....????

[MickFlanagen]

Good Morning (well..not really)

Below is a macro that I have been using very reliably for months now. Admittedly, I am pretty new to writing macros, but I still don't understand why this macro worked yesterday, and won't work today. Here it is:

Application.ScreenUpdating = False
    Sheets("Bloomberg Data").Select
    Application.Run "BLPLinkReset"
    Range("A1").Select
    Workbooks.Open Filename:= _
        "T:\usequty\Audit Reporting\T-Cross Reports\tcross.xls"
    Application.Run "BLPLinkReset"
    Application.Run "ConnectChartEvents"
    Application.Run "'New Template.xls'!DelCASHCONV"
    ActiveWindow.Close
    Sheets("bloomberg data").Select
    Application.Run "BLPLinkReset"
    Range("A1:P1").Select
    Selection.AutoFill Destination:=Range("A1:P1000"), Type:=xlFillDefault              ******this is the line that is giving me trouble.
    Range("A1:P1000").Select
    Workbooks.Open Filename:= _
        "T:\usequty\Audit Reporting\T-Cross Reports\tcross.xls"
    Application.Run "BLPLinkReset"
    Application.Run "ConnectChartEvents"
    ActiveWindow.Close
    Application.Run "BLPLinkReset"

    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("m2:O1000").NumberFormat = "hh:mm"
    
    Sheets("Report").Select
    Application.Run "BLPLinkReset"
    Range("A6:S6").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A6:S1007"), Type:=xlFillDefault
    Range("A6:S1007").Select
    Range("H6:H1007").Select
    Selection.NumberFormat = "mm/dd/yyyy"
    
    
   
    Range("A5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=20, Criteria1:="1"
    Range("J5").Select
    
    Range("A2:K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    myRange = Selection.Address
    ActiveSheet.PageSetup.PrintArea = myRange
                
    Application.ScreenUpdating = True
    
    SvName = Sheets("Report").Range("C3")
    Application.Dialogs(xlDialogSaveAs).Show SvName
    
    
End Sub

*** the cause of the trouble seems to be that it is skipping the line "Range("A1:P1").Select". The error I get is "Run-time error '1004': Autofill method of Range class failed."

If I debug, select it manually and restart it it works until I get to:

Selection.AutoFill Destination:=Range("A6:S1007"), Type:=xlFillDefault
    Range("A6:S1007").Select
    Range("H6:H1007").Select
    Selection.NumberFormat = "mm/dd/yyyy"
    
    
   
    Range("A5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=20, Criteria1:="1"
    Range("J5").Select
    
    Range("A2:K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    myRange = Selection.Address
    ActiveSheet.PageSetup.PrintArea = myRange
                
    Application.ScreenUpdating = True
    
    SvName = Sheets("Report").Range("C3")
    Application.Dialogs(xlDialogSaveAs).Show SvName
    
    
End Sub

The error I am getting here is the same:
"Run-time error '1004': Autofill method of Range class failed."

I could really use some help with this, as I have no idea why my (formerly) perfectly good macro suddenly stopped working!

Thanks in advance,

Mick

 

[Firefly2012]

Hi Mick

Where in your workbook do you have this code? Is it in a sheet module, the ThisWorkbook module or in a standard code module?

Have you copied this code to another module (where it is now not working)?

 

[MickFlanagen]

Thanks for your reply Firefily. To be perfectly honest, I'm not really sure. I believe it is stored in "this workbook" (when I go to Tools->Macros->Macros in: it is in This workbook). Does that make sense? In my VBA window, on the left hand side, it is in a list of modules, and is in Module 8.

That being said, I haven't moved it or changed it in anyway. All I do is open the file in the morning, run the macro, SaveAs, and close it.

Thanks again for the help.

Mick

 

[Firefly2012]

Is the Bloomberg addin loaded with macros enabled?

 

[MickFlanagen]

yup, my Bloomberg addin seems to be working fine.

 

[Firefly2012]

Can you try modifying the code (in the first error region) with:

Range("A1:P1000").UnMerge
Range("A1:P1").Select
    Selection.AutoFill Destination:=Range("A1:P1000"), Type:=xlFillDefault              ******this is the line that is giving me trouble.
    Range("A1:P1000").Select

and try re-running

 

[MickFlanagen]

Thanks. I tried that, and it didn't seem to help so I resorted to this fix:

1) curse under your breath
2) close excel
3) open excel
4) try again
5) curse openly
6) close excel
7) open excel
8) try again
9) curse so the whole office can hear it
10) close excel
11) open excel
12) try again

after that it worked. it seems like excel is on the 12 step program....