I want a macro that will at the start of a year change, for example,
I've created the steps that on 1/1/2015 would give me d = 2015, so that I can write
y = "cash " & d & ".xlsx", which yields cash 2015.xlsx (with no quotes).
If I then write
Excel won't run either one.
VBA also doesn't recognize:
Workbooks(""cash " & d & ".xlsx"").Activate
Workbooks('"cash " & d & ".xlsx"').Activate
Workbooks("'cash ' & d & '.xlsx').Activate
You can use:
Workbooks("cash" & d & ".xlsx").Activate
y = "cash " & d & ".xlsx" Workbooks(y).activate
If you put quotes around your variables they become strings and are intepreted as just
Also, it's best to come up with names for your variables so the next person that has to maintain your code doesn't hunt you down at your new job and stab your eyeballs out with a pair of chopsticks while screaming obscenities at you.
If you want to break down the old name and rename it with a new year, try this.
As long as the form is "AAAA ####.ext" with a space between the name and year.
This will take the old name, break out the year, add one, then Save As the new name.
Sub RenameNewYear() Dim oldPath As String, path As String, OldName As String, fileExt As String Dim tempYear As Long, newYear As Long Dim old() As String Dim Filename As String, FileNPath As String path = ActiveWorkbook.path 'C:\users\owner\desktop OldName = ActiveWorkbook.Name '"cash 2015.xlsm" 'Split original name up into sections. old = Split(OldName, ".") 'old(0) = "cash 2014", old(1) = "xlsm" fileExt = "." & old(1) '.xlsm old = Split(old(0), " ") 'old(0) = "cash", old(1) = "2014" tempYear = CLng(old(1)) '2014 newYear = tempYear + 1 '2015 Filename = (old(0) & " " & newYear & fileExt) '"cash 2015.xlsm" FileNPath = path & "\" & Filename 'C:\users\owner\desktop\cash 2015.xlsm ActiveWorkbook.SaveAs fileName:=FileNPath Sheets("sum").Activate End Sub
To open it you'd use this: But it would already be open, as the last code saves As the new name.