We help IT Professionals succeed at work.

EOD Data Generator

Medium Priority
428 Views
Last Modified: 2013-06-16
Hi Experts,

Need VBA code for data to update as new data is available...pls see attached file....


Thank You
Data-Generator.xlsm
Comment
Watch Question

you could use this code when placed in the "ThisWorkbook"  VBA section.
please not however that new worksheets will be generated for names that are not known yet.
This means that typographical errors will lead to new worksheets with single entries, so this can be used without problems on automated data scrapers but take care when using manual input !

the current data makes for 49 added sheets.
Sub process_new_data()
Dim src As Worksheet
Dim dst As Worksheet
Dim row As Range
Dim name As String


    '-- locate source data with updates
    Set src = Worksheets("EOD")
    
    '-- parse each row with updated data
    For Each row In src.UsedRange.Rows
        '-- skip header rows
        If row.Cells(1) <> "Name" Then
            name = row.Cells(1)
            Application.StatusBar = "Updating " & name
            '-- locate destination sheet
            If Not exists(name) Then
                MsgBox "No worksheet found for " & name & ", generating one"
                Set dst = Worksheets.Add
                dst.name = name
                dst.Range("a1") = name
                dst.Range("A1:F1").Merge
                dst.Range("A2") = "Date"
                dst.Range("B2") = "Open"
                dst.Range("C2") = "High"
                dst.Range("D2") = "Low"
                dst.Range("E2") = "Close"
                dst.Range("F2") = "Volume"
             Else
                Set dst = Worksheets(name)
            End If
            '-- move data to appropriate sheet
            dst.Rows(3).Insert
            src.Range("B" & row.row & ":G" & row.row).Copy dst.Range("A" & 3)
            Application.DisplayAlerts = False
            row.Delete xlShiftToLeft
            Application.DisplayAlerts = True
        End If
    Next row
    

End Sub

Private Function exists(sheet As String) As Boolean
Dim result As Boolean

    result = False
    On Error Resume Next
    result = (Worksheets(sheet).name = sheet)
    On Error Resume Next
    exists = result
End Function

Open in new window

as an example the "ACC LTD     " worksheet contains 5 spaces which are not present in the source data.

Author

Commented:
ok sorry this is rectified file but i add some space for EOD Study will you put code in this workbook pls ......


Thank You Very Very Much
Data-Generator.xlsm
posted the macro code with a slight adaptation in your file.

the macro inserts new data at row 23 to take into account the added space.
Data-Generator--1-.xlsm

Author

Commented:
ok i have created desired file but want macro key i.e Ctrl+o pls add it
Data-Generator.xlsm
[Ctrl-o] added as macro key.

By the way: You can easily do this yourself via the 'developer' tab: press the 'macro' button, select the macro, press 'options...'
Data-Generator--2-.xlsm

Author

Commented:
Pretty Good

Author

Commented:
pls attend my next question it is same as this .....


thank you

Author

Commented:
Hi akoster, question is added data generator V2.....


Same workbook but little modification

pls look in to that,

Thank You

Explore More ContentExplore courses, solutions, and other research materials related to this topic.