runtime error 1004

[merlin777]

Hi everyone - hope you're having a good Christmas!

I have a work book containing macros. One of the macros orders values in a column in a table (a one-button lazy way of using the auto filter). This was a recorded macro.

Until today it worked fine but I changed the filename to include a date and running this macro started to give me runtime error 1004. I run the debugger and get this:

Sub highestprofit()
'
' highestprofit Macro
'
'
    Application.Run "newportfolio.xlsm!YahooFinance2"
    Application.Run "newportfolio.xlsm!YahooFinance2"
    ActiveWorkbook.Worksheets("portfolio").ListObjects("portfoliotable").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("portfolio").ListObjects("portfoliotable").Sort. _
        SortFields.Add Key:=Range("portfoliotable[[#All],[PROFIT" & Chr(10) & "%]]"), SortOn:= _
        xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("portfolio").ListObjects("portfoliotable").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

The line that was highlighted I have coloured red. I can seel the problem.... recording the macro has caused the workbook's filename to be used in the code and now the filename has changed I'm getting an error. I guess that's the down side of recording macros and not tidying them up.

I could just change the filename wherever it appears but I'll have to do that every time I change the filename for every recorded macro I have. Please could someone tell me how to modify these lines of code so they are independent of the filename?

Many thanks!

 

[Joe4]

If you have a workbook solely with macros, why would the name of it be changing?
If the macros are stored within the workbook they are being used, you don't need to include the file name in calling it.
Note that you can also store commonly used macros in your Personal Macro Workbook. These are accessible to your when you log in to your computer with your account whenever you have Excel open.
Or, you could store your Macros in an Add-In.

 

[merlin777]

Hi, Joe. Thanks for your reply. I'm afraid I don't quite understand your first sentence.

This macro and a few other similar are very specific to this workbook so there would be nothing to gain by putting it in my PMW.

I understand that using the file name is unnecessary but as it was a recorded macro, this is the code that resulted. Mt VBA knowledge doesn't extend to changing those first 2 lines to remove the filename, hence me needing to ask for help.

I have no idea at all why the macro recorder repeated those first lines code, either.

 

[Joe4]

I'm afraid I don't quite understand your first sentence.

Sorry, that was a typo. It should read:

If you have a workbook solely with macros, why would the name of it be changing?

So, is YahooFinance2 just another procedure that resides within the same workbook as this other VBA code?
If so, then just run it using Call, i.e.

Call YahooFinance2[/COLOR]

You only need Run when running macros in other Workbooks.

 

[merlin777]

Hi, Joe. The workbook tracks stocks and shares using live data for yahoo finance so it's not just a book of macros. yahoofinance2 is part of the code I was given by someone to get the data from yahoo. I'm not quite sure what it does but please find it below:

Sub YahooFinance2()
Dim r As Range
Dim SymbolList As Range
Dim LastRow As Long
Dim wb As Workbook, wb1 As Workbook
Dim ws As Worksheet, ws2 As Worksheet
Dim Tickers As String, Codes As String
Set wb = ThisWorkbook
LastRow = wb.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
Set SymbolList = wb.Sheets(1).Range("A1:A" & LastRow)
Set ws = wb.Sheets("returneddata")
Application.StatusBar = "Processing..."
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each r In SymbolList
    If Tickers <> "" Then
        Tickers = Tickers & "+" & r.Value
    Else
        Tickers = r.Value
    End If
Next r
Codes = wb.Sheets(1).Range("B1")
ws.Range("A1").CurrentRegion.ClearContents
Application.Workbooks.Open ("http://download.finance.yahoo.com/d/quotes.csv?s=" & Tickers & "&f=" & Codes)
Set wb1 = ActiveWorkbook
wb1.Sheets(1).Range("A1").CurrentRegion.Copy Destination:=ws.Range("A1")
wb1.Close savechanges:=False
ws.Columns.AutoFit
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.StatusBar = ""
Call RunAgain
End Sub

I've no idea why it's appeared - twice - at the start of this macro. All the macro does is to sort one table column into largest to smallest.

 

[merlin777]

Ah - I've just had a brainwave. yahoofinance2 runs automatically about one a minute. Is it possible that it ran while I was recording the macro and the macro recorder added it into the recorded macro?

 

[merlin777]

I've just removed those 2 lines and the error has gone.