Solved: Emailing by broker

[Klartigue]

Please see the attached document. Is there a way to say that if in Column W, the broker is fidelity, then open the "Fidelity spreadsheet master.xls" spreadsheet?

And then I am going to format the blotter and copy and paste some things in "Fidelity spreadsheet master.xls" and after pasting it in there I would like the macro to automatically "save as" and save the spreadsheet as Fidelity allocations "todays date" and "time".xls

Can you help out with that?

Thanks again for the help!

[Bob Phillips]

If Application.Countif(Columns("W"),"Fidelity") > 0 Then

Workbooks.Open "C:\My Data\Fidelity spreadsheet master.xls"
End If

[Klartigue]

Is there a code that will do a "save as" so i can save my doc.. I would like to save as "Fidelity allocation.xls with todays date & time"?

[Klartigue]

Please see the attached document. I have written this code in attempt to cut all the rows that have "fidelity" in column w and insert place them in another sheet on the same workbook. But my code isnt working in terms of cutting and pasting all the rows in a new sheet for all the rows with "fidelity" in column W..hmmm....

Sub FIDO()

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 4 To LastRow

If Cells(i, "W").Value = "Fidelity" Then
.Rows(i).Cut

End If
Next i
End With
End Sub

[Klartigue]

here is the attachment

[Klartigue]

I got it to save as :

Sub Saveas()
'
'
With ActiveSheet
'
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades&today's date& time.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End With

End Sub

[Klartigue]

but in the above code, today's date and time are not working..what are the codes for those?

[Klartigue]

Please see the attached document. I have written this code in attempt to cut all the rows that have "fidelity" in column w and insert place them in another sheet on the same workbook. But my code isnt working in terms of cutting and pasting all the rows in a new sheet for all the rows with "fidelity" in column W..hmmm....

Now i am working with this but still cant figure it out...

Sub FIDO()

Dim LastRow As Long
Dim i As Long
With ActiveSheet

LastRow = .Cells(.Rows.Count, "W").End(xlUp).Row
For i = 4 To LastRow

If Application.CountIf(.Range("A:W").Resize(i), .Cells(i, "W").Value) = Fidelity Then
End If
With .Range("W").Select
Selection.Cut
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Selection.Paste
End With
Next i
End With
End Sub

[Bob Phillips]

I got it to save as :

Sub Saveas()
'
'
With ActiveSheet
'
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades&today's date& time.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End With

End Sub


Sub Saveas()
'
'
With ActiveSheet
'
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades" & Format(Now,"yyyymmdd hhmmss") & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End With

End Sub

I will have a go at the other bit tomorrow, it is late here now.

[Klartigue]

Thanks for the above response! That works great!

Also, please see the attached document. There is a text box explaining what I would like to be able to do.. I know I ask a ton of questions but I appreciate all the help!

[Klartigue]

Oh wait,, back to the save as time and date..it says compile error: Ambiguous name detected, and its saying that about the word "Format"

Sub Saveas()
'
With ActiveSheet
'
ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades" & Format(Now, "yyyymmdd hhmmss") & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End With

End Sub

[Klartigue]

Any ideas on the date and time format and the copying and pasting of rows that have "fidelity" in column W?

See above posts.

Thanks

[Klartigue]

I figured out how to copy and paste the fidelity data:

Sub FIDO()
' Copy and paste fidelity allocations into new sheet in workbook

Dim LastRow As Long
Dim i As Long
With ActiveSheet

LastRow = .Cells(.Rows.Count, "W").End(xlUp).Row
For i = 4 To LastRow

If .Cells(i, "W").Value = "FIDELITY" Then

With Range(.Cells(i, "A"), .Cells(i, "W")).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Blotter").Select
End With

End If

Next i

End With

End Sub