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.)
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?
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"), 51That 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, 51You 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 wsHope this helps.