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
230 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

707 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

20 Experts available now in Live!

Get 1:1 Help Now