How to create a macro that saves a spreadsheet under a new name

[Jonseya]

Hi

Could anybody help me with a problem, I am trying to create a macro in an excel spreadsheet that will save the file under a certain name but each time the macro is run it incriments the number each time. i.e. the first time the file is run it will be Part1.xls then part2.xls and then part3.xlsetc.

 

[PHV]

Why not posting your actual code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Jonseya]

The only code that I have is below which is just a basic recording of save as

Sub save2()

ActiveWorkbook.SaveAs Filename:="C:\part1.xls"
End Sub

 

[PHV]

You may try this:
Sub save2()
With ActiveWorkbook
If InStr(.Name, "part1") = 0 Then
.SaveAs Filename:="C:\part1.xls"
Else
.Save
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pbail1]

Hi Johnseya

If I understand you correctly you want the counter at the end of the filename incremented each time you save and that you will be saving a new version of the last version of the spreadsheet, i.e. whatever the last partX was created and not the original Part0 spreadsheet?

If so try this:

Sub savefile()
    
    Dim Filename As String
    Dim Filelength As Long
    Dim FileLocation As String
    Dim Counter As Long
    
    Filename = ActiveWorkbook.Name
    FileLocation = "C:\"
    
    If Left(Filename, 4) <> "Part" Then
    
        ActiveWorkbook.SaveAs FileLocation & "Part1"
    
    Else
        Filename = Left(Filename, Len(Filename) - 4)
        Filelength = Len(Filename) - 4
        Counter = CLng(Right(Filename, Filelength))
        
        Counter = Counter + 1
        
        ActiveWorkbook.SaveAs FileLocation & "Part" & Counter
        
    End If


End Sub

 

[Jonseya]

cheers for the help its all sorted now.

Thank you