Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

incrementing numbers of added files names

0

Hi experts

I need  rename  by increment numbers like result1,result2,result3  when add new  file  every  time  while runnig macro      . the current  macro  makes file RESULT  and  when  run  the  macro repeatedly will replace of  the  old  file  has already created . so  when  run  the  code  should  increment numbers .

 any  chance  to  do that,please?

Answer
Discuss

Answers

0
Selected Answer

Kalil

Rather than name the saved files as result1, result2, result3 etc., I suggest you make the numeric part something more meaningful. In the attached revised file, I've changed the SaveAs line to add a date/ time suffix to the filename: 

        Application.DisplayAlerts = False
        .SaveAs .Path & "\Result " & Format(Now, "yyyymmdd hh-mm"), 51

That means the file name will be something like Result 20220627 12-58.xlsx so you will always know that the file was saved at 12:58 on 27 June 2022 (even if it is re-saved later).

If however you insist on result1, result2, result3 etc., I suggest you save the "last saved number" in a cell on a hidden worksheet, say B1 in a sheet called Index. Then when you run your macro, increment that (and save the .xlsm file to store that) and add that number to the SaveAs line this:

.SaveAs .Path & "\Result " & Worksheets("Index"). Range("B1").Value, 51

 You will first need to skip that hidden worksheet in your loop, so change your With line to read:

For Each ws In .Worksheets
     If ws.Name<>"Index" Then 
          With ws.Cells(1).CurrentRegion
          .
          . << existing code>>
          .
          End With

     End if
Next ws

Hope this helps.

Discuss

Discussion

thanks for give me two choices . of course the first choice is suitable for me .
much appreciated for your help.
Kalil (rep: 28) Jun 27, '22 at 10:11 am
Great! Thanks for selecting my Answer, Kalil.
John_Ru (rep: 3992) Jun 27, '22 at 11:09 am
Just corrected my Answer to replace two instances of the word "page" with "hidden worksheet" (for the second option).
John_Ru (rep: 3992) Jun 27, '22 at 11:11 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login