[VBA]Sub Merrill()
' Copy and paste merrill allocations into new sheet in workbook
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "X").End(xlUp).Row
For i = 4 To lastrow
If .Cells(i, "X").Value = "MERRILL LYNCH" Then
With Range(.Cells(i, "A"), .Cells(i, "X")).Select
Selection.Cut
Sheets("Merrill").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Blotter").Select
End With
End If
Next i
End With
End Sub[/VBA]
[VBA] Sub Amegy()
' Copy and paste Amegy allocations into new sheet in workbook
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "X").End(xlUp).Row
For i = 4 To lastrow
If .Cells(i, "X").Value = "AMEGY" Then
With Range(.Cells(i, "A"), .Cells(i, "X")).Select
Selection.Cut
Sheets("AMEGY").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Blotter").Select
End With
End If
Next i
End With
End Sub [/VBA]
[VBA] Sub MerrillAccounts()
Dim NumBlocks As Long
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
.Cells(i, "A").Offset(0, 17).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-9],'[Account numbers.xls]Book1'!R1C1:R772C8,4,FALSE)"
Next i
End With
End Sub
Sub PasteAccounts()
'
Columns("R:R").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("R:R").Select
Application.CutCopyMode = False
Selection.Cut
Columns("I").Select
ActiveSheet.Paste
Range("I1").Select
ActiveCell.FormulaR1C1 = "Account Number"
Columns("I:I").Select
Columns("I:I").EntireColumn.Autofit
End Sub
Sub PasteValues100()
'
' Paste values
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Sub OpenSheet()
' Open avalon spreadsheet to upload merrill trades
Workbooks.Open Filename:= _
"G:\Katherine Lartigue\Allocations\Avalon Fixed Income.xls"
End Sub
Sub TradesML()
' activate Trades window
Windows("Trades.xls").Activate
End Sub
Sub COPYALLOCations()
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1:Q2705").Select
Selection.Copy
Windows("Avalon Fixed Income.xls").Activate
Application.Run "BLPLinkReset"
Range("A5").Select
ActiveSheet.Paste
End Sub
Sub Autofit()
'
' Autofit Columns
Columns("A:Q").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:P").EntireColumn.Autofit
Columns("A:P").EntireColumn.Autofit
Columns("A:P").EntireColumn.Autofit
Columns("A:P").EntireColumn.Autofit
Columns("A:P").EntireColumn.Autofit
Columns("A:P").EntireColumn.Autofit
Columns("A:P").EntireColumn.Autofit
End Sub
Sub newcashtest()
Dim NumBlocks As Long
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 5 To lastrow
If .Cells(i, "A").Value = Date Then
NumBlocks = NumBlocks + 1
End If
If NumBlocks Mod 2 = 1 Then
.Rows(i).Resize(, 20).Select
With Selection.Interior
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
End With
End If
Next i
End With
End Sub
Sub DeleteBlock2()
'
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
End Sub
Sub ProcessData4()
Dim lastrow As Long
Dim endat As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = lastrow To 5 Step -1
endat = i
Do While .Cells(i - 1, "C").Value = .Cells(i, "C").Value And i - 1 >= 5
i = i - 1
Loop
.Rows(endat + 1).Insert
.Cells(endat + 1, "A") = "Total Quantity:"
.Cells(endat + 1, "I").FormulaR1C1 = "=SUM(R[-1]C:R" & i & "C)"
.Rows(i).Resize(2).Insert
.Cells(i + 1, "A").Value = "Trade Allocation:"
Next i
End With
End Sub
Sub FixFormat4()
Rows("5:5").Select
Selection.Delete Shift:=xlUp
Range("A5:P5").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
End Sub
Sub Cashtrades()
Dim lr As Long, r As Long
Dim cell As Range
Dim str1 As String, str2 As String
ActiveWorkbook.Sheets(1).Activate
lr = Range("A65000").End(xlUp).Row
For Each cell In Range("A5:A" & lr)
r = cell.Row
If cell.Text <> "" And (StrComp(cell.Text, cell.Offset(0, 1).Text, vbTextCompare) = 0) Then
Range("A" & r & ":P" & r).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
Set del = cell
del.Offset(0, 16).FormulaR1C1 = "CASH TRADE"
End With
End If
Next cell
End Sub
Sub NumberMerrill()
Range("I6:M2082").Select
ActiveWindow.SmallScroll Down:=-75
Selection.NumberFormat = "#,##0.00"
End Sub
Public Sub SaveMerrill1()
Application.DisplayAlerts = False
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:= _
"G:\Katherine Lartigue\Allocations\Merrill Lynch Trades.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
End Sub
Public Sub SaveMerrill()
If Range("A5").Value = "" Then
ActiveWorkbook.Saved = True
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
Else
Application.DisplayAlerts = False
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:= _
"G:\Katherine Lartigue\Allocations\Merrill Lynch Trades.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
End If
ActiveWindow.Close
End Sub
Sub EmailMerrill()
Const FILE_ATTACH As String = _
"G:\Katherine Lartigue\Allocations\Merrill Lynch Trades.xls"
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim lastrow As Long
Dim bodyText As String
Dim i As Long
Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True
Set oMailItem = oOutlook.CreateItem(0)
With oMailItem
Set oRecipient = .Recipients.Add("mms_tradeallocationsteam@ml.com")
oRecipient.Type = 1
.CC = "mms_avalonserviceteam@ml.com"
.Subject = "Avalon Trade Allocations Attached"
.body = "Please see the attached trade allocations." & vbNewLine & vbNewLine & _
"Let me know if you need anything else." & vbNewLine & vbNewLine & _
"Thanks, " & vbNewLine & vbNewLine & _
"Katherine Lartigue " & vbNewLine & _
"klartigue@avalonadvisors.com " & vbNewLine & _
"713-238-2088" & vbNewLine & vbNewLine & _
"FYI - For any trades done with Bank of Oklahoma (BOK), prime broker clears DTC 0443." & vbNewLine & _
"For any trades done with Bank of Oklahoma (BOK DTC 0901), they have an overnight DTC confirm process so you will not see a confirm until tomorrow. " & _
"Therefore, for any cash trades with BOK, please build manually. Also, when making delivery to BOK, reference internal account 000220" & vbNewLine & vbNewLine & _
"For any trades done with the Merrill trading desk (Broker code BA), I have had them reference account #16h02272, just as they have done in the past. Please make sure these trades flow through" & vbNewLine & vbNewLine & _
"The confidentiality of internet email cannot be guaranteed. Please do not include private or confidential information such as passwords, account numbers, Social Security numbers, etc. in e-mails to Avalon Advisors, LLC. If you think you are the victim of fraud or identity theft, contact Avalon Advisors immediately."
If Dir(FILE_ATTACH, vbNormal) <> "" Then
.Attachments.Add ("G:\Katherine Lartigue\Allocations\Merrill Lynch Trades.xls")
.Save
End If
End With
End Sub[/VBA]
Reply With Quote
