PDA

View Full Version : Solved: Emailing by broker



Klartigue
04-02-2012, 08:04 AM
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
04-02-2012, 10:20 AM
If Application.Countif(Columns("W"),"Fidelity") > 0 Then

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

Klartigue
04-04-2012, 09:18 AM
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
04-04-2012, 09:35 AM
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
04-04-2012, 09:54 AM
here is the attachment

Klartigue
04-04-2012, 12:37 PM
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
04-04-2012, 12:45 PM
but in the above code, today's date and time are not working..what are the codes for those?

Klartigue
04-04-2012, 01:15 PM
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
04-04-2012, 03:20 PM
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
04-05-2012, 06:44 AM
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
04-05-2012, 07:12 AM
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
04-09-2012, 11:54 AM
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
04-10-2012, 12:29 PM
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