• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

EOD Data Generator

Hi Experts,

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


Thank You
Data-Generator.xlsm
0
itjockey79
Asked:
itjockey79
  • 5
  • 5
1 Solution
 
Arno KosterCommented:
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.
0
 
Arno KosterCommented:
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

0
 
Arno KosterCommented:
as an example the "ACC LTD     " worksheet contains 5 spaces which are not present in the source data.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
itjockey79Author 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
0
 
Arno KosterCommented:
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
0
 
itjockey79Author Commented:
ok i have created desired file but want macro key i.e Ctrl+o pls add it
Data-Generator.xlsm
0
 
Arno KosterCommented:
[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
0
 
itjockey79Author Commented:
Pretty Good
0
 
itjockey79Author Commented:
pls attend my next question it is same as this .....


thank you
0
 
itjockey79Author Commented:
Hi akoster, question is added data generator V2.....


Same workbook but little modification

pls look in to that,

Thank You
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now