[Solved] Saving: Recording a Macro to Save a File As 'X

Hi Reese,

You have a number of options here for use as your workbook counter:
1. A cell in the workbook.
2. A text file.
3. A name.

I've opted for the latter and attached an example workbook. For those unable to download workbooks the code is as follows:

Sub SaveMe()
    Const strPath As String = "E:\Downloads"
    'change to "y:/Dellserver/Newdell d/Users/rhagos" or whatever
    Const strName As String = "'Cancellation Filing RR "
    Dim iCnt As Integer
    Dim strSaveName As String
    
    iCnt = GetNameValue + 1
    StoreValueInName (iCnt)
    'increment and record the counter using a name
    strSaveName = strPath & "\" & strName & iCnt
    ThisWorkbook.SaveAs FileName:=strSaveName
    'save the workbook with the desired name
    
End Sub


Sub StoreValueInName(i As Integer)
    Names.Add Name:="StoredNumber", RefersTo:=i
End Sub


Function GetNameValue() As Integer
    'storevalue must have been run first
    Dim i As Integer
    On Error Resume Next
    i = Evaluate("StoredNumber")
    On Error GoTo 0
    GetNameValue = i
End Function

As regards learning VBA, how about trying some of Dave's training? (see this link : http://www.ozgrid.com/Training…icrosoftExcelTraining.htm ) Or you can just try and pick it up for yourself (don't be afraid to make full use of the VBE Help files and also the Macro Recorder - both are great tools and are often overlooked). Feel free to keep asking questions here as your learning develops.

HTH

Below is modified from code that I use. It checks a directory for the next available number. If file xxx0001 and xxx0002 exist then it will save the existing file as xxx0003. (I haven't checked this mod for bugs). This allows multiple users to create sequenced filenames in the same directory.

Sub SaveMacro()

aReg = "Cancellation Filing RR "
aDir = "y:\whatever\"
aRegLen = Len(aReg) + 1

mLargest = 0
folderspec = aDir

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 In fc
s = s & f1.Name
s = s & vbCrLf
If Right(f1.Name, 4) = ".xls" Then
m = Val(Mid(f1.Name, aRegLen, 4))
If m > mLargest Then
mLargest = m
End If
End If
Next

mLargest = mLargest + 1
L = Len(mLargest)
Select Case L
Case 1
aFile = "000" & mLargest & ".xls"
Case 2
aFile = "00" & mLargest & ".xls"
Case 3
aFile = "0" & mLargest & ".xls"
Case 4
aFile = mLargest & ".xls"
End Select

aSaveAs = aDir & aReg & aFile

Msg = "File will be saved as:" & Chr(13) _
& aSaveAs & Chr(13) & Chr(13) & "If this is incorrect then Cancel"
Style = vbOKCancel + vbQuestion
Title = "File to be saved and numbered via automatic sequencing"
response = MsgBox(Msg, Style, Title)

If response = vbOK Then
ActiveWorkbook.SaveAs Filename:=aSaveAs
Else
End
End If
End Sub

Ok Reese, I made some minor modifications to the code above and it does work.

1) Open the Visual Basic Editor (Tools-Macro-Visual Basic Editor)
2) Find & click on your VBAProject (your file name)
3) Copy the sub SaveMacro code to a module (ie Module1) within your project - if you don't see a module then you can create one by clicking on Insert-Module.
4) change the aReg= and aDir= lines in the SaveMacro code to be your file name prefix and your save-directory location.
5) If you go back to your spreadsheet you can invoke the macro by clicking on Tools-Macro-Macros-SaveMacro-Run
(it should work - crosses fingers :))
6) you can make an "ALT-a" short-cut by clicking on Tools-Macro-Macros-SaveMacro-Options then enter the "A" or "a" - remember -it is case sensitive.
Good luck. Let me know how it turns out.