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:
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!
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:
Rich (BB code):
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
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!