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:

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

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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