|
▼ヤマ さん: こんにちわ
こんなんでどうでしょう?
MYDAYには適切な値を入力してください。
ファイルの末尾の番号は、無限ループが怖いので3000に制限しています。
Private Sub test()
Dim DirName As String
Dim FileName As String
Dim SaveName As String
Dim i As Integer
Dim MYDAY As Date
MYDAY = 1 '適切な値を入力してください。
DirName = "S:\GFM\Equity_Finance\Blotter\" & _
Format(MYDAY, "MMMYY") & "\"
With ActiveWorkbook.ActiveSheet
If .Range("B1") = "New" Then
FileName = DirName & "TRADE_BLOTTER" & _
Format(MYDAY, "DD_MM") & " " & .Range("C5") & "_NEW.XLS"
ElseIf .Range("B10") = "Substituition" Then
FileName = DirName & "TRADE_BLOTTER" & _
Format(MYDAY, "DD_MM") & " " & .Range("C13") & "_SUB.XLS"
End If
End With
SaveName = FileName
For i = 1 To 3000
If Dir(SaveName) = "" Then Exit For
SaveName = Mid(FileName, 1, InStrRev(FileName, ".XLS") - 1) & _
i & ".XLS"
Next i
ActiveWorkbook.SaveAs SaveName
End Sub
|
|