Thread: Macro that can obtain data daily from a chart and append to a row

Hi everyone,
I am learning about macros and would like to include them in my work. I need some help with a project. I have a file already, but it is too big to post. I am willing to email it to anyone who wants to help me.

I am working on am excel file that analyzes index prices. It monitors personal aspects I designed as triggers to make decisions on buying and selling funds.

I would like to be able to open the excel and have itself update itself. I have a solution now, but it take daily action on my part. If I didn't do it for few days it would be nice if it did it itself.

I am looking to append the closing prices of the funds to my already established chart at the bottom. I used DATA>FromWeb to get the last two days of data.(I used the last two days in an early attempt for my project) It would be nice to be able to use DATA>FromWeb, get 20 to 30 days of data, and then let the macro decide when the last update was and where it needs to begin its append operation.

I would appreciate any help on this and am willing to answer question to clear anything confusing up. My current solution comes from a modified version of this thread "http://www.excelforum.com/excel-programming/754483-macro-to-append-data.html"

Hopefully I can get some help soon.

Thanks guys and gals,
Josh

JapanDave,
I would like to try that, but I am still very new to macros. I added that as a new macro in VBA, below my original macro. Does it now know how to run on its own? Can I just close my excel files and VBA and it will update at 12:00:00 daily ? A little explaining may help thanks.
Josh

Sure can, I will also include the files. You can see I did some test runs just changing the data in book1 and seeing if book2 works properly. Thanks

Option Explicit
Sub UpDate_Book2()
Application.OnTime TimeValue("15:01:00"), "UpDate_Book2"
    Dim wbData As Workbook
    Dim wbTarget As Workbook
    Dim wsData As Worksheet
    Dim wsTarget As Worksheet
    Dim LR As Long
    Dim MyPath As String
    Application.ScreenUpdating = False
    MyPath = ActiveWorkbook.Path
    Set wbData = ThisWorkbook
    Application.Workbooks.Open (MyPath & "\" & "Book2.xlsx")
    Set wbTarget = Workbooks("Book2.xlsx")
    For Each wsData In wbData.Sheets
        For Each wsTarget In wbTarget.Sheets
            If wsTarget.Name = wsData.Name Then
                wsData.Range("A1,H1:L1").Copy Destination:=wsTarget.Range("A1")
                LR = wsTarget.Range("A" & Rows.Count).End(xlUp).Row + 1
                wsData.Range("A2,H2:L2").Copy Destination:=wsTarget.Range("A" & LR)
                Exit For
            End If
        Next wsTarget
    Next wsData
    wbTarget.Close savechanges:=True
    Application.ScreenUpdating = True
End Sub
Sub AutoUpdate_Book2()
Application.OnTime TimeValue("12:25:00"), "UpDate_Book2"
End Sub