Vba

[ana1991]

Good morning

I have a question regarding VBA and bloomberg. I want to automatically get a table from bloomberg, with various countries, dates, events...
Then I want to filter the main table (the one I get from bloomberg) by country, copying the filtered table into a new sheet for each country and sort each new table by date.
Is there a way to do it?


Thanks in advance!

Here is an example of the bloomberg table:


[TABLE="width: 528"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Tickers[/TD]
[TD]Country[/TD]
[TD]Event[/TD]
[TD]Date[/TD]
[TD]Period[/TD]
[TD]Median[/TD]
[TD]Average[/TD]
[TD]Actual [/TD]
[TD]Prior[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Brazil[/TD]
[TD]XXX[/TD]
[TD]01-06-2015[/TD]
[TD]SEP[/TD]
[TD="align: right"]0,68[/TD]
[TD="align: right"]0,30[/TD]
[TD="align: right"]0,22[/TD]
[TD="align: right"]0,09[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Portugal[/TD]
[TD]XXX[/TD]
[TD]02-06-2015[/TD]
[TD]SEP[/TD]
[TD="align: right"]0,97[/TD]
[TD="align: right"]0,77[/TD]
[TD="align: right"]0,06[/TD]
[TD="align: right"]0,83[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Euro Zone[/TD]
[TD]XX1[/TD]
[TD]03-06-2015[/TD]
[TD]SEP[/TD]
[TD="align: right"]0,32[/TD]
[TD="align: right"]0,64[/TD]
[TD="align: right"]0,68[/TD]
[TD="align: right"]0,23[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Spain[/TD]
[TD]XX2[/TD]
[TD]04-06-2015[/TD]
[TD]AUG[/TD]
[TD="align: right"]0,82[/TD]
[TD="align: right"]0,23[/TD]
[TD="align: right"]0,67[/TD]
[TD="align: right"]0,15[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Turkey[/TD]
[TD]XXX[/TD]
[TD]05-06-2015[/TD]
[TD]SEP[/TD]
[TD="align: right"]0,79[/TD]
[TD="align: right"]0,25[/TD]
[TD="align: right"]0,07[/TD]
[TD="align: right"]0,43[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]France[/TD]
[TD]XX5[/TD]
[TD]06-06-2015[/TD]
[TD]SEP[/TD]
[TD="align: right"]0,72[/TD]
[TD="align: right"]0,44[/TD]
[TD="align: right"]0,13[/TD]
[TD="align: right"]0,84[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]Germany[/TD]
[TD]XX7[/TD]
[TD]07-06-2015[/TD]
[TD]AUG[/TD]
[TD="align: right"]0,44[/TD]
[TD="align: right"]0,38[/TD]
[TD="align: right"]0,04[/TD]
[TD="align: right"]0,23[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]Romania[/TD]
[TD]XX1[/TD]
[TD]08-06-2015[/TD]
[TD]AUG[/TD]
[TD="align: right"]0,24[/TD]
[TD="align: right"]0,21[/TD]
[TD="align: right"]0,75[/TD]
[TD="align: right"]0,10[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]Germany[/TD]
[TD]XX3[/TD]
[TD]09-06-2015[/TD]
[TD]SEP[/TD]
[TD="align: right"]0,88[/TD]
[TD="align: right"]0,03[/TD]
[TD="align: right"]0,89[/TD]
[TD="align: right"]0,31[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]Portugal[/TD]
[TD]XX8[/TD]
[TD]10-06-2015[/TD]
[TD]JUL[/TD]
[TD="align: right"]0,44[/TD]
[TD="align: right"]0,80[/TD]
[TD="align: right"]0,97[/TD]
[TD="align: right"]0,37[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]Euro Zone[/TD]
[TD]XX6[/TD]
[TD]11-06-2015[/TD]
[TD]SEP[/TD]
[TD="align: right"]0,87[/TD]
[TD="align: right"]0,72[/TD]
[TD="align: right"]0,14[/TD]
[TD="align: right"]0,27[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]Spain[/TD]
[TD]XXX[/TD]
[TD]12-06-2015[/TD]
[TD]SEP[/TD]
[TD="align: right"]0,52[/TD]
[TD="align: right"]0,03[/TD]
[TD="align: right"]0,80[/TD]
[TD="align: right"]0,90[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD]Brazil[/TD]
[TD]XX0[/TD]
[TD]13-06-2015[/TD]
[TD]AUG[/TD]
[TD="align: right"]0,34[/TD]
[TD="align: right"]0,39[/TD]
[TD="align: right"]0,46[/TD]
[TD="align: right"]0,27[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]Germany[/TD]
[TD]XX8[/TD]
[TD]14-06-2015[/TD]
[TD]SEP[/TD]
[TD="align: right"]0,23[/TD]
[TD="align: right"]0,90[/TD]
[TD="align: right"]0,04[/TD]
[TD="align: right"]0,73[/TD]
[/TR]
[TR]
[TD]O[/TD]
[TD]Euro Zone[/TD]
[TD]XX2[/TD]
[TD]15-06-2015[/TD]
[TD]SEP[/TD]
[TD="align: right"]0,24[/TD]
[TD="align: right"]0,01[/TD]
[TD="align: right"]0,16[/TD]
[TD="align: right"]0,19[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]UK[/TD]
[TD]XXX[/TD]
[TD]16-06-2015[/TD]
[TD]SEP[/TD]
[TD="align: right"]0,07[/TD]
[TD="align: right"]0,02[/TD]
[TD="align: right"]0,91[/TD]
[TD="align: right"]0,77[/TD]
[/TR]
</tbody>[/TABLE]

 

[Arjan77]

Start with this to create your sheets:

Sub CAddSheets()
Dim count() As Variant

Set Rng1 = Range("B2:B10000")
     
   For Each Cell In Rng1
   If Cell.Value = "" Then GoTo First
   
   ReDim Preserve count(0 To x) As Variant
   
   For i = 0 To x - 1
   If Cell.Value = count(i) Then
   GoTo First
   End If
   Next i
     
   count(x) = Cell.Value
   x = x + 1

First:
   Next
   
For i = 0 To UBound(count) - 1

Worksheets.Add(, Worksheets(Worksheets.count)).Name = count(i)
Next

   
End Sub

 

[Arjan77]

Copy this code in a module, paste your source data on Sheet1 and run the macro

Sub CAddSheets()
Dim Count() As Variant
Dim Ws As Worksheet
Dim WsDest As Worksheet

Application.ScreenUpdating = False


Set Ws = Worksheets("Sheet1")

Set Rng1 = Range("B2:B10000")
     
   For Each Cell In Rng1
   If Cell.Value = "" Then GoTo First
   
   ReDim Preserve Count(0 To x) As Variant
   
   For i = 0 To x - 1
   If Cell.Value = Count(i) Then
   GoTo First
   End If
   Next i
     
   Count(x) = Cell.Value
   x = x + 1

First:
   Next
   
For i = 0 To UBound(Count) - 1
If ChWs(Count(i)) = True Then
Worksheets.Add(, Worksheets(Worksheets.Count)).Name = Count(i)
End If
Next
Ws.Select

For i = 0 To UBound(Count) - 1
Set WsDest = Worksheets(Count(i))
Ws.Range("A1:I1").Select
Selection.Copy
WsDest.Range("A1").PasteSpecial xlPasteAll
Next

Ws.Select

For i = 0 To UBound(Count) - 1
Ws.Cells(1, 2).Select
Selection.AutoFilter Field:=2, Criteria1:=Count(i)

Ws.Range("A2").Select
Ws.Range(Selection, Selection.End(xlDown)).Select
Ws.Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Set WsDest = Worksheets(Count(i))
WsDest.Range("A2").PasteSpecial xlPasteAll
WsDest.Range("D2:D10000").Sort WsDest.Range("D2"), xlAscending
Next
Ws.Cells(1, 2).Select
Selection.AutoFilter Field:=2

Application.ScreenUpdating = True
End Sub

Function ChWs(ShName As Variant)
For Each Worksheet In ThisWorkbook.Worksheets
If Worksheet.Name = ShName Then
ChWs = False
Exit Function
End If
Next
ChWs = True
End Function