incremental file name

[kathy]

Do you know how I would create a macro that would do the
following

I have a cell that increments everytime a macro is run
(1, 2, 3 etc....) - I want a macro that will then save
this # as part of the file name

file1.xls
file2.xls
file3.xls etc......

If not I would have to rely on the user saving the file
as file1, file2 etc......

If you know of a way to do this it would be greatly
appreciated - I've looked on the web and have yet to find
anything

Thanks

 

[Ed Ferrero]

Hi kathy,

If the cell containing the file number is A1 then

myPath = ActiveWorkbook.Path
ActiveWorkbook.SaveAs myPath & "\myFileName" & Range("A1")

 

[Jason Morin]

Asssuming columns A-M, try:

=SUMPRODUCT(--(COUNTIF(INDIRECT(CHAR(ROW(INDIRECT
("65:77")))&"1:"&CHAR(ROW(INDIRECT("65:77")))
&"65536"),">0")>0))

where the "65:77" represents columns A-M. Of course this
evaluates every row of the column, which I doubt is
necessary and wastes memory. Lower the 65536 in the
formula to a realistic number.

HTH
Jason
Atlanta, GA

 

[Jason Morin]

Sorry - responded to the wrong post.

-----Original Message-----
Asssuming columns A-M, try:

=SUMPRODUCT(--(COUNTIF(INDIRECT(CHAR(ROW(INDIRECT
("65:77")))&"1:"&CHAR(ROW(INDIRECT("65:77")))
&"65536"),">0")>0))

where the "65:77" represents columns A-M. Of course this
evaluates every row of the column, which I doubt is
necessary and wastes memory. Lower the 65536 in the
formula to a realistic number.

HTH
Jason
Atlanta, GA

.

 

[Jason Morin]

This macro worked for me (feedback from VBA programmers
appreciated). It'll add an underscore plus the number in
cell Sheet!A1. If there is no number, it'll add "_1" to
the file name, assuming this is the first save.

Sub SaveThis()
Dim sName As String
Dim sStr As String
Dim sExt As String
Dim sLoc As Integer
Dim sCell As Range
Dim sWb As Workbook

Set sWb = ActiveWorkbook
Set sCell = Sheets("Sheet1").Range("A1")
sLoc = InStr(ActiveWorkbook.Name, "_")

If sLoc = 0 Then

sExt = Application.WorksheetFunction.Substitute _
(sWb.Name, ".xls", "")
sWb.SaveAs Filename:=sExt & "_1"

Else

sStr = Left(sWb.Name, sLoc - 1)
sName = sStr & "_" & sCell
sWb.SaveAs sName

End If
End Sub