?
Solved

How can I write a macro that can save as a file with a date that is today -1 every day when i run it?

Posted on 2008-10-31
4
Medium Priority
?
289 Views
Last Modified: 2013-11-27
I've recorded a macro that deletes specific columns on each tab and at the end I would like the macro to save the file as the date today-1. I run this macro everyday.
0
Comment
Question by:hugoohta
  • 2
3 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22851795
1) What kind of file are you saving?

2) Please post the code so far
0
 

Author Comment

by:hugoohta
ID: 22851906
1) I'm trying to save a report

2) Sub DeleteColumn()
'
' DeleteColumn Macro
' Macro recorded 28/10/2008 by suzukia
'

'
    ActiveWindow.ActivateNext
    Application.Run "BLPLinkReset"
    Columns("D:F").Select
    Selection.Cut
    ActiveWindow.SmallScroll ToRight:=69
    Columns("FK:FK").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.SmallScroll ToRight:=-67
    Columns("G:H").Select
    Selection.Cut
    ActiveWindow.SmallScroll ToRight:=68
    Columns("FK:FK").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.SmallScroll ToRight:=-46
    Columns("DQ:EC").Select
    Selection.Cut
    ActiveWindow.ScrollColumn = 133
    ActiveWindow.ScrollColumn = 135
    ActiveWindow.ScrollColumn = 138
    ActiveWindow.ScrollColumn = 142
    ActiveWindow.ScrollColumn = 149
    ActiveWindow.ScrollColumn = 153
    ActiveWindow.ScrollColumn = 162
    ActiveWindow.ScrollColumn = 163
    Columns("FK:FK").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.SmallScroll ToRight:=-67
    Sheets("Index Delta").Select
    Application.Run "BLPLinkReset"
    Columns("D:F").Select
    Selection.Cut
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 83
    ActiveWindow.ScrollColumn = 95
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 99
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 122
    ActiveWindow.ScrollColumn = 134
    ActiveWindow.ScrollColumn = 141
    ActiveWindow.ScrollColumn = 143
    ActiveWindow.ScrollColumn = 144
    ActiveWindow.ScrollColumn = 146
    ActiveWindow.ScrollColumn = 147
    ActiveWindow.SmallScroll ToRight:=1
    Columns("EW:EW").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.SmallScroll ToRight:=-29
    Columns("G:H").Select
    Selection.Cut
    ActiveWindow.SmallScroll ToRight:=28
    Columns("EW:EW").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.ScrollColumn = 146
    ActiveWindow.ScrollColumn = 145
    ActiveWindow.ScrollColumn = 144
    ActiveWindow.ScrollColumn = 143
    ActiveWindow.ScrollColumn = 142
    ActiveWindow.ScrollColumn = 139
    ActiveWindow.ScrollColumn = 135
    ActiveWindow.ScrollColumn = 130
    ActiveWindow.ScrollColumn = 127
    ActiveWindow.ScrollColumn = 113
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 91
    ActiveWindow.ScrollColumn = 88
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 89
    ActiveWindow.ScrollColumn = 90
    ActiveWindow.ScrollColumn = 91
    ActiveWindow.ScrollColumn = 92
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 94
    Columns("CW:DJ").Select
    Columns("CW:DJ").EntireColumn.AutoFit
    Columns("DC:DJ").Select
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 108
    ActiveWindow.ScrollColumn = 107
    ActiveWindow.ScrollColumn = 106
    ActiveWindow.ScrollColumn = 104
    ActiveWindow.ScrollColumn = 102
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 102
    Columns("DJ:DW").Select
    Columns("DJ:DW").EntireColumn.AutoFit
    Columns("DC:DO").Select
    Selection.Cut
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 110
    ActiveWindow.ScrollColumn = 111
    ActiveWindow.ScrollColumn = 113
    ActiveWindow.ScrollColumn = 114
    ActiveWindow.ScrollColumn = 116
    ActiveWindow.ScrollColumn = 117
    ActiveWindow.ScrollColumn = 119
    ActiveWindow.ScrollColumn = 121
    ActiveWindow.ScrollColumn = 122
    ActiveWindow.ScrollColumn = 123
    ActiveWindow.ScrollColumn = 124
    ActiveWindow.ScrollColumn = 126
    ActiveWindow.ScrollColumn = 127
    ActiveWindow.ScrollColumn = 129
    ActiveWindow.ScrollColumn = 135
    ActiveWindow.ScrollColumn = 137
    ActiveWindow.ScrollColumn = 138
    ActiveWindow.ScrollColumn = 140
    ActiveWindow.ScrollColumn = 141
    ActiveWindow.ScrollColumn = 143
    ActiveWindow.ScrollColumn = 144
    ActiveWindow.ScrollColumn = 145
    ActiveWindow.ScrollColumn = 146
    ActiveWindow.ScrollColumn = 147
    ActiveWindow.ScrollColumn = 148
    ActiveWindow.ScrollColumn = 149
    Columns("EW:EW").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.ScrollColumn = 148
    ActiveWindow.ScrollColumn = 147
    ActiveWindow.ScrollColumn = 146
    ActiveWindow.ScrollColumn = 145
    ActiveWindow.ScrollColumn = 144
    ActiveWindow.ScrollColumn = 143
    ActiveWindow.ScrollColumn = 141
    ActiveWindow.ScrollColumn = 140
    ActiveWindow.ScrollColumn = 139
    ActiveWindow.ScrollColumn = 136
    ActiveWindow.ScrollColumn = 135
    ActiveWindow.ScrollColumn = 132
    ActiveWindow.ScrollColumn = 131
    ActiveWindow.ScrollColumn = 128
    ActiveWindow.ScrollColumn = 127
    ActiveWindow.ScrollColumn = 125
    ActiveWindow.ScrollColumn = 124
    ActiveWindow.ScrollColumn = 123
    ActiveWindow.ScrollColumn = 122
    ActiveWindow.ScrollColumn = 121
    ActiveWindow.ScrollColumn = 120
    ActiveWindow.ScrollColumn = 114
    ActiveWindow.ScrollColumn = 113
    ActiveWindow.ScrollColumn = 112
    ActiveWindow.ScrollColumn = 111
    ActiveWindow.ScrollColumn = 110
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 108
    ActiveWindow.ScrollColumn = 107
    ActiveWindow.ScrollColumn = 106
    ActiveWindow.ScrollColumn = 105
    ActiveWindow.ScrollColumn = 103
    ActiveWindow.ScrollColumn = 102
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 93
    Columns("CX:DI").Select
    Selection.EntireColumn.Hidden = True
    Sheets("AllDelta").Select
    Application.Run "BLPLinkReset"
    Columns("D:F").Select
    Selection.Cut
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 137
    ActiveWindow.ScrollColumn = 142
    ActiveWindow.ScrollColumn = 157
    ActiveWindow.ScrollColumn = 159
    ActiveWindow.ScrollColumn = 161
    ActiveWindow.ScrollColumn = 162
    ActiveWindow.ScrollColumn = 163
    ActiveWindow.ScrollColumn = 164
    ActiveWindow.ScrollColumn = 166
    ActiveWindow.ScrollColumn = 167
    ActiveWindow.ScrollColumn = 168
    ActiveWindow.ScrollColumn = 169
    ActiveWindow.ScrollColumn = 171
    ActiveWindow.ScrollColumn = 172
    ActiveWindow.ScrollColumn = 174
    ActiveWindow.ScrollColumn = 178
    ActiveWindow.ScrollColumn = 180
    ActiveWindow.ScrollColumn = 182
    ActiveWindow.ScrollColumn = 185
    ActiveWindow.ScrollColumn = 186
    ActiveWindow.ScrollColumn = 188
    ActiveWindow.ScrollColumn = 190
    ActiveWindow.ScrollColumn = 191
    ActiveWindow.ScrollColumn = 192
    ActiveWindow.ScrollColumn = 193
    ActiveWindow.ScrollColumn = 195
    ActiveWindow.ScrollColumn = 197
    ActiveWindow.ScrollColumn = 198
    ActiveWindow.ScrollColumn = 200
    ActiveWindow.ScrollColumn = 202
    ActiveWindow.ScrollColumn = 203
    ActiveWindow.ScrollColumn = 205
    ActiveWindow.ScrollColumn = 206
    ActiveWindow.SmallScroll ToRight:=1
    Columns("HD:HD").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.ScrollColumn = 206
    ActiveWindow.ScrollColumn = 203
    ActiveWindow.ScrollColumn = 201
    ActiveWindow.ScrollColumn = 197
    ActiveWindow.ScrollColumn = 193
    ActiveWindow.ScrollColumn = 188
    ActiveWindow.ScrollColumn = 184
    ActiveWindow.ScrollColumn = 179
    ActiveWindow.ScrollColumn = 175
    ActiveWindow.ScrollColumn = 170
    ActiveWindow.ScrollColumn = 166
    ActiveWindow.ScrollColumn = 163
    ActiveWindow.ScrollColumn = 159
    ActiveWindow.ScrollColumn = 156
    ActiveWindow.ScrollColumn = 153
    ActiveWindow.ScrollColumn = 150
    ActiveWindow.ScrollColumn = 149
    ActiveWindow.ScrollColumn = 134
    ActiveWindow.ScrollColumn = 131
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    Columns("G:H").Select
    Selection.Cut
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 139
    ActiveWindow.ScrollColumn = 153
    ActiveWindow.ScrollColumn = 161
    ActiveWindow.ScrollColumn = 165
    ActiveWindow.ScrollColumn = 170
    ActiveWindow.ScrollColumn = 173
    ActiveWindow.ScrollColumn = 177
    ActiveWindow.ScrollColumn = 179
    ActiveWindow.ScrollColumn = 183
    ActiveWindow.ScrollColumn = 184
    ActiveWindow.ScrollColumn = 185
    ActiveWindow.ScrollColumn = 188
    ActiveWindow.ScrollColumn = 189
    ActiveWindow.ScrollColumn = 191
    ActiveWindow.ScrollColumn = 192
    ActiveWindow.ScrollColumn = 196
    ActiveWindow.ScrollColumn = 198
    ActiveWindow.ScrollColumn = 201
    ActiveWindow.ScrollColumn = 203
    ActiveWindow.ScrollColumn = 206
    ActiveWindow.ScrollColumn = 207
    Columns("HD:HD").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.ScrollColumn = 205
    ActiveWindow.ScrollColumn = 204
    ActiveWindow.ScrollColumn = 202
    ActiveWindow.ScrollColumn = 200
    ActiveWindow.ScrollColumn = 198
    ActiveWindow.ScrollColumn = 197
    ActiveWindow.ScrollColumn = 195
    ActiveWindow.ScrollColumn = 194
    ActiveWindow.ScrollColumn = 193
    ActiveWindow.ScrollColumn = 191
    ActiveWindow.ScrollColumn = 190
    ActiveWindow.ScrollColumn = 189
    ActiveWindow.ScrollColumn = 188
    ActiveWindow.ScrollColumn = 186
    ActiveWindow.ScrollColumn = 185
    ActiveWindow.ScrollColumn = 184
    ActiveWindow.ScrollColumn = 183
    ActiveWindow.ScrollColumn = 181
    ActiveWindow.ScrollColumn = 180
    ActiveWindow.ScrollColumn = 179
    ActiveWindow.ScrollColumn = 177
    ActiveWindow.ScrollColumn = 176
    ActiveWindow.ScrollColumn = 175
    ActiveWindow.ScrollColumn = 174
    ActiveWindow.ScrollColumn = 172
    ActiveWindow.ScrollColumn = 171
    ActiveWindow.ScrollColumn = 170
    ActiveWindow.ScrollColumn = 169
    ActiveWindow.ScrollColumn = 167
    ActiveWindow.ScrollColumn = 165
    ActiveWindow.ScrollColumn = 163
    ActiveWindow.ScrollColumn = 162
    ActiveWindow.ScrollColumn = 161
    ActiveWindow.ScrollColumn = 160
    ActiveWindow.ScrollColumn = 161
    ActiveWindow.ScrollColumn = 162
    ActiveWindow.ScrollColumn = 163
    ActiveWindow.ScrollColumn = 165
    Columns("FJ:FV").Select
    Selection.Cut
    ActiveWindow.ScrollColumn = 176
    ActiveWindow.ScrollColumn = 179
    ActiveWindow.ScrollColumn = 180
    ActiveWindow.ScrollColumn = 181
    ActiveWindow.ScrollColumn = 185
    ActiveWindow.ScrollColumn = 188
    ActiveWindow.ScrollColumn = 190
    ActiveWindow.ScrollColumn = 194
    ActiveWindow.ScrollColumn = 197
    ActiveWindow.ScrollColumn = 198
    ActiveWindow.ScrollColumn = 199
    ActiveWindow.ScrollColumn = 200
    ActiveWindow.ScrollColumn = 202
    ActiveWindow.ScrollColumn = 204
    ActiveWindow.ScrollColumn = 207
    ActiveWindow.ScrollColumn = 208
    Columns("HD:HD").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.SmallScroll ToRight:=-70
    ChDir "Y:\Ldn\Mkts\RiskFinance\CDO\Trading\RiskReports"
    ActiveWorkbook.SaveAs Filename:= _
        "Y:\Ldn\Mkts\RiskFinance\CDO\Trading\RiskReports\Global Bespoke_Confirmed_27-Oct-2008_Correlation Desk Risk Report Breakdown.xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWindow.ActivateNext
    Application.Run "BLPLinkReset"
End Sub
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 200 total points
ID: 22851942
Change this:

    ActiveWorkbook.SaveAs Filename:= _
        "Y:\Ldn\Mkts\RiskFinance\CDO\Trading\RiskReports\Global Bespoke_Confirmed_27-Oct-2008_Correlation Desk Risk Report Breakdown.xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

to:

    ActiveWorkbook.SaveAs Filename:= _
        "Y:\Ldn\Mkts\RiskFinance\CDO\Trading\RiskReports\Global Bespoke_Confirmed_" & _
        Format(Now - 1, "yyyy-mm-dd") & "_Correlation Desk Risk Report Breakdown.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False



I used yy-mm-dd to guarantee that files sort in chronological order.  To keep you same format...

    ActiveWorkbook.SaveAs Filename:= _
        "Y:\Ldn\Mkts\RiskFinance\CDO\Trading\RiskReports\Global Bespoke_Confirmed_" & _
        Format(Now - 1, "d-mmm-yyyy") & "_Correlation Desk Risk Report Breakdown.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False


or to force two digits for the day...

    ActiveWorkbook.SaveAs Filename:= _
        "Y:\Ldn\Mkts\RiskFinance\CDO\Trading\RiskReports\Global Bespoke_Confirmed_" & _
        Format(Now - 1, "dd-mmm-yyyy") & "_Correlation Desk Risk Report Breakdown.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question