Skipping part of a code

[Klartigue]

See the below code. Is there a way to say that if this statement is not true, If .Cells(i, "T").Value = "HOUSTON TRUST" Then
then skip the entire step?

Sub CreateHoustonTrustSheet()
'Name next sheet Houston Trust
Sheets.Add After:=Sheets(Sheets.Count)
Application.Run "BLPLinkReset"
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Houston Trust"
Sheets("Blotter").Select
Application.Run "BLPLinkReset"
' Move all Houston Trust allocations to a new sheet

Dim Lastrow As Long
Dim i As Long
With ActiveSheet

Lastrow = .Cells(.Rows.Count, "T").End(xlUp).Row
For i = 2 To Lastrow

If .Cells(i, "T").Value = "HOUSTON TRUST" Then

With Range(.Cells(i, "A"), .Cells(i, "T")).Select
Selection.Cut
Sheets("Houston Trust").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Blotter").Select
End With

End If

Next i

End With
' Open avalon spreadsheet to upload Houston Trust trades
Workbooks.Open Filename:= _
"G:\Katherine Lartigue\Allocations\Avalon Fixed Income.xls"

' activate Trades window
Windows("Trades.xls").Activate

'Paste houston trust allocations into spreadsheet
With ActiveWindow
.Top = 121
.Left = 19.75
End With
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1:V767").Select
Selection.Copy
Windows("Avalon Fixed Income.xls").Activate
Application.Run "BLPLinkReset"
ActiveSheet.Paste
End Sub

[Bob Phillips]

Do you mean just change the = to <>?

[Klartigue]

Is that all it takes? I mean to just write something that says if the word "fidelity" is not present in any cells in column T, then skip that whole step. Because this step is meant to pull out all the fidelity trades and paste them into a new sheet but if there are no fidelity trades there is nothing to pull out. So in the event that there are none, i dont want there to be an error, I would just like the code to continue on to the next part.

[Aussiebear]

You are currently trying to loop through all the cells in column T to find the value "HOUSTON TRUST", with this section of code.

Dim Lastrow As Long
Dim i As Long
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "T").End(xlUp).Row
For i = 2 To Lastrow
If .Cells(i, "T").Value = "HOUSTON TRUST" Then
With Range(.Cells(i, "A"), .Cells(i, "T")).Select
Selection.Cut
Sheets("Houston Trust").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Blotter").Select
End With
End IF
Next i
End With
If It doesn't find the value then it skips to the next cell in the column without carrying out the code in lines 7 to 13.

[Bob Phillips]

Is that all it takes? I mean to just write something that says if the word "fidelity" is not present in any cells in column T, then skip that whole step. Because this step is meant to pull out all the fidelity trades and paste them into a new sheet but if there are no fidelity trades there is nothing to pull out. So in the event that there are none, i dont want there to be an error, I would just like the code to continue on to the next part.

Is Fidelity the only thing in the cell or would it be part of more text?

If the former, wrap this test around your code


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

'your code
End If

If the latter, then


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

'your code
End If

[Klartigue]

So do it like this?

Sub Trades()
If Application.CountIf(.Columns("T"), "Fidelity") > 0 Then

'your code
End If
' Save blotter
With ActiveSheet
ChDir "C:\Users\klartigue\Desktop"
ActiveWorkbook.Saveas Filename:="C:\Users\klartigue\Desktop\Trades.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End With

' name sheet
ActiveWindow.WindowState = xlMaximized
Sheets.Add After:=Sheets(Sheets.Count)
Application.Run "BLPLinkReset"
Sheets("Blotter").Select
Application.Run "BLPLinkReset"
' name column
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Q3").Select
ActiveCell.FormulaR1C1 = "Total Block Quantity"

' block quantity
Dim Lastrow As Long
Dim i As Long
With ActiveSheet

Lastrow = .Cells(.Rows.Count, "W").End(xlUp).Row
For i = 4 To Lastrow
With .Cells(i, "Q").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-16]=""Block"",RC[1],R[-1]C)"
Range("Q4").Select

End With
Next i
End With

' Macro3 Macro
Columns("Q:Q").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Delete block allocations

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 1 Step -1

If Cells(i, "A").Value2 = "Block" Then

.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True


' Copy and paste fidelity allocations into new sheet in workbook

With ActiveSheet

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

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

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

End If

Next i

End With


'Sort trades by broker
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Range("A4:Y6").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Blotter").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Blotter").Sort.SortFields.Add Key:=Range("X4:X91") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Blotter").Sort
.SetRange Range("A3:X91")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' delete row a
Sheets("Sheet1").Select

Rows("1:1").Select
Selection.Delete Shift:=xlUp

' label columns
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
With ActiveWindow
End With
Range("G1").Select
ActiveCell.FormulaR1C1 = "Trade Date"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Settle Date"
Columns("I:I").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 2.29
Columns("J:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 17.86
Range("J1").Select
ActiveCell.FormulaR1C1 = "Master Account"
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 2.57
Columns("L:L").ColumnWidth = 3.14
Range("M1").Select
ActiveCell.FormulaR1C1 = "Cusip/Symbol"

' format a through H
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("D:E").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 1.29
Columns("H:H").Select
Selection.Cut
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("M:M").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "B/S"
Range("H2").Select

' format
Columns("E:E").ColumnWidth = 2.29
Columns("F:F").ColumnWidth = 2.14
Columns("I:AA").Select
Selection.Cut
Range("AC1").Select
ActiveSheet.Paste
Columns("I:I").ColumnWidth = 0.67
Columns("J:J").ColumnWidth = 0.67
Columns("K:K").ColumnWidth = 0.58
Columns("L:L").ColumnWidth = 0.58
Columns("M:M").ColumnWidth = 0.42
Columns("N:N").ColumnWidth = 0.42
Columns("O:O").ColumnWidth = 0.67
Columns("P:P").ColumnWidth = 0.58
Columns("Q:Q").ColumnWidth = 0.58
Columns("R:R").ColumnWidth = 0.67
Columns("S:S").ColumnWidth = 0.5
Columns("T:T").ColumnWidth = 0.5
Columns("U:U").ColumnWidth = 0.58
Columns("V:V").ColumnWidth = 0.33
Columns("W:W").ColumnWidth = 0.42
Columns("X:X").ColumnWidth = 0.42
Columns("Y:Y").ColumnWidth = 0.33
Columns("Z:Z").ColumnWidth = 0.33
Columns("AA:AA").ColumnWidth = 0.5
Columns("AB:AB").ColumnWidth = 0.5

' more formatting
Columns("AK:AK").Select
Selection.Cut
Columns("AC:AC").Select
Selection.Insert Shift:=xlToRight
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Price"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Executing Broker MPID"
' move quantity
Columns("AO:AO").Select
Selection.Cut
Columns("AC:AC").Select
Selection.Insert Shift:=xlToRight
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Block Quantity"
'tweek format

Columns("AF:AF").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("AF1").Select
ActiveCell.FormulaR1C1 = "Executing Broker DTC #"
Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("AG1").Select
ActiveCell.FormulaR1C1 = "Time"
Columns("AW:AW").Select
Selection.Cut
Columns("AH:AH").Select
ActiveSheet.Paste
Range("AH1").Select
ActiveCell.FormulaR1C1 = "Interest"
Columns("AI:AI").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AR:AR").Select
Selection.Cut
Columns("AJ:AJ").Select
Selection.Insert Shift:=xlToRight
Columns("AI:AI").ColumnWidth = 3.14
Range("AJ1").Select
ActiveCell.FormulaR1C1 = "ClientAcct"
Columns("AK:AK").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AU:AU").Select
Selection.Cut
Columns("AL:AL").Select
Selection.Insert Shift:=xlToRight
Range("AL1").Select
ActiveCell.FormulaR1C1 = "Quantity"
Columns("AJ:AJ").Select
Selection.ColumnWidth = 10.86
Columns("AK:AK").ColumnWidth = 1.71
Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 9.43
Columns("AG:AH").Select
Selection.EntireColumn.Hidden = True
Columns("AK:AK").ColumnWidth = 2.71
Columns("AO:AZ").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft

' insert fidelity master account number
Dim NumBlocks As Long

With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
.Cells(i, "A").Offset(0, 3).Select
ActiveCell.Value = "911754798"
Next i
End With

' Fit columns
Columns("A:A").ColumnWidth = 9.14
Columns("B:B").ColumnWidth = 10

'format numbers
Columns("D:D").Select
Selection.NumberFormat = "General"
' open broker codes
Workbooks.Open Filename:= _
"G:\Katherine Lartigue\Instructions\Broker Codes.xlsx"

' activate blotter
Windows("Trades.xls").Activate

' Look up broker DTC numbers

With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow


.Cells(i, "A").Offset(0, 31).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Broker Codes.xlsx]Sheet1'!R1:R1048576,3,FALSE)"

Next i
End With

'clear contents of column
Columns("AB:AB").Select
Selection.ClearContents

' Open Alob sheet
Workbooks.Open Filename:= _
"G:\Katherine Lartigue\Allocations\Account numbers.xls"

' Lookup entire fidelity account numbers
Windows("Trades.xls").Activate


With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
.Cells(i, "A").Offset(0, 41).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-4],'[Account numbers.xls]Book1'!R1:R65536,4,FALSE)"
Next i
End With

' open fidelity spreadsheet
Workbooks.Open Filename:= _
"G:\Katherine Lartigue\Allocations\Fidelity spreadsheet master.xls"

' Copy and paste values in spreadsheet
Windows("Trades.xls").Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("Q17").Select


'Paste account numbers as values
Range("AP2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("AL2").Select
ActiveSheet.Paste


' Copy and Paste data into Fidelity master spreadsheet

Range("A2:AN252").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Windows("Fidelity spreadsheet master.xls").Activate
Application.Run "BLPLinkReset"
Range("A9").Select
ActiveSheet.Paste

' savefile


ChDir "G:\Katherine Lartigue\Allocations"
ActiveWorkbook.Saveas Filename:="Fidelity Trades.xls", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

If Application.CountIf(.Columns("T"), "*Fidelity*") > 0 Then

'your code
End If
End Sub

I basically need it to say if there is no "Fidelity" in column T, then go on to the next step which is the exact step as above except this time it will be searching for the word "merrill" .

[Bob Phillips]

Who are you asking, Ted or me, as you don't seem to have implemented what I suggested.

[Bob Phillips]

You can ditch all of those

Activewindow.ScrollRow

lines.

What are all of theb

Range(Selection, Selection.End(xlDown)).Select

for? Won't just the one suffice?

This code


Range("G1").Select
ActiveCell.FormulaR1C1 = "Trade Date"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Settle Date"
Columns("I:I").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 2.29
Columns("J:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 17.86
Range("J1").Select
ActiveCell.FormulaR1C1 = "Master Account"
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 2.57
Columns("L:L").ColumnWidth = 3.14
Range("M1").Select
ActiveCell.FormulaR1C1 = "Cusip/Symbol"


can be compacted down to


Range("G1").Value = "Trade Date"
Range("H1").Value1 = "Settle Date"
With Columns("I:I")
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.ColumnWidth = 2.29
End With
With Columns("J:J")
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.ColumnWidth = 17.86
Range("J1").Value = "Master Account"
End With
With Columns("K:K")
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.ColumnWidth = 2.57
End With
Columns("L:L").ColumnWidth = 3.14
Range("M1").Value = "Cusip/Symbol"


and this code

Columns("E:E").ColumnWidth = 2.29
Columns("F:F").ColumnWidth = 2.14
Columns("I:AA").Select
Selection.Cut
Range("AC1").Select
ActiveSheet.Paste
Columns("I:I").ColumnWidth = 0.67
Columns("J:J").ColumnWidth = 0.67
Columns("K:K").ColumnWidth = 0.58
Columns("L:L").ColumnWidth = 0.58
Columns("M:M").ColumnWidth = 0.42
Columns("N:N").ColumnWidth = 0.42
Columns("O:O").ColumnWidth = 0.67
Columns("P:P").ColumnWidth = 0.58
Columns("Q:Q").ColumnWidth = 0.58
Columns("R:R").ColumnWidth = 0.67
Columns("S:S").ColumnWidth = 0.5
Columns("T:T").ColumnWidth = 0.5
Columns("U:U").ColumnWidth = 0.58
Columns("V:V").ColumnWidth = 0.33
Columns("W:W").ColumnWidth = 0.42
Columns("X:X").ColumnWidth = 0.42
Columns("Y:Y").ColumnWidth = 0.33
Columns("Z:Z").ColumnWidth = 0.33
Columns("AA:AA").ColumnWidth = 0.5
Columns("AB:AB").ColumnWidth = 0.5

can be reduced to

Columns("I:AA").Cut
Range("AC1").Paste
Columns("E:F").ColumnWidth = Array(2.29, 2.14)
Columns("I:AB").ColumnWidth = Array(0.67, 0.67, 0.58, 0.58, 0.42, 0.42, 0.67, _
0.58, 0.58, 0.67, 5, 0.5, 58, 0.33, 0.42, _
0.42, 0.33, 0.33, 0.5, 0.5)

[Klartigue]

See the attached document. I have run these steps so far, and they resulted in what you see in the attached document.

Sub Trades()
' Save blotter
With ActiveSheet
ChDir "C:\Users\klartigue\Desktop"
ActiveWorkbook.Saveas Filename:="C:\Users\klartigue\Desktop\Trades.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End With

End Sub
Sub NameSheet()
'
ActiveWindow.WindowState = xlMaximized
Sheets.Add After:=Sheets(Sheets.Count)
Application.Run "BLPLinkReset"
Sheets("Blotter").Select
Application.Run "BLPLinkReset"
End Sub
Sub NameColumn()
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Q3").Select
ActiveCell.FormulaR1C1 = "Total Block Quantity"
End Sub

Sub BlockQuantity()
'
Dim Lastrow As Long
Dim i As Long
With ActiveSheet

Lastrow = .Cells(.Rows.Count, "W").End(xlUp).Row
For i = 4 To Lastrow
With .Cells(i, "Q").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-16]=""Block"",RC[1],R[-1]C)"
Range("Q4").Select

End With
Next i
End With
End Sub
Sub QuantityValues()
' Macro3 Macro
Columns("Q:Q").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Public Sub ProcessData()

'Delete block allocations

Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 1 Step -1

If Cells(i, "A").Value2 = "Block" Then

.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True

End Sub



Now the next step of my macro is below.

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, "X").End(xlUp).Row
For i = 4 To Lastrow

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

With Range(.Cells(i, "A"), .Cells(i, "X")).Select
Selection.Cut
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

But as you can see, "Fidelity" does not exist in column x. So, is there a code that skips the below code and moves on to the next code, and moves onto the next SUB...END SUB which invovles finding all the merrill trades?

[Klartigue]

Actually, I have a better way to this is. Is there a code that says,

if cell A9 is blank, than close the workbook without saving it.

But if cell A9 is not blank, the save the workbook per the below code:

Public Sub Saveas()
todaysdate = Application.InputBox("Enter Today's Date for File Name", "Today's Date")


ChDir "G:\Katherine Lartigue\Allocations"
ActiveWorkbook.Saveas Filename:="Fidelity Trades" + " " + "(" + todaysdate + ")" + ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


End Sub

[Bob Phillips]

I am sorry, there are things I am not understanding, and I ask a question and you don't yanswer. You then post a file from which I am supposed to work it all out. So I am at a loss to know what is what.

[Klartigue]

Sorry. Im only focusing on this post now because I restructured how I was doing things. In my workbook, Trades.xls, is there a way to say that if cell A2 is blank, than close the workbook without saving it?

[Aussiebear]

You could try the following:

Sub CloseWorkbook()
If Range("A2").Value = "" Then
ActiveWorkbook.Close True
End IF
End Sub

[Klartigue]

That works great to close the workbook, thanks! Now is there a way to combine the two below vba codes and to say that if A2 is blank than close the workbook but if A2 is not blank the save the workbook ?

Sub CloseWorkbookAmegy()
If Range("A5").Value = "" Then
ActiveWorkbook.Close True
End If
End Sub

Public Sub SaveAmegy()
todaysdate = Application.InputBox("Enter Today's Date for File Name", "Today's Date")


ChDir "G:\Katherine Lartigue\Allocations"
ActiveWorkbook.Saveas Filename:="Amegy Trades" + " " + "(" + todaysdate + ")" + ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

ActiveWindow.Close

End Sub