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
254 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
4 Comments
 
LVL 92

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 92

Accepted Solution

by:
Patrick Matthews earned 50 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access on Mouse move 5 30
Excel Formula to split product code 5 14
Turn off MS Access Default=0 for Numerics 6 24
Dropbox in Windows Server 2008 4 27
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

813 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now