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
268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

749 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