Save copy with ascending numbers

[realrookie]

Hello,

Could someone please tell me what code to add in the following macro, so it saves a copy of the workbook with ascending number? e.g: wb-1.xls and if wb-1 already exists it will save as wb-2.xls and so on?

Please Login or Register  to view this content.

Regards,

RR

[LJMetzger]

Hi realrookie,

See the attached file which contains the following code. This should help you get started. Instead of -1, -2, the sequence numbers are -001,-002, etc. When viewing file names in Windows Explorer, the files sort better with the leading zeros.

In an ordinary code module such as Module1:

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Please let me know if you need any additional help.

Lewis

NOTE:
The attached file may not work in all situations. I recommend not downloading this file, and download the file associated with post #6 in this thread instead.

[realrookie]

Hello LJMetzger,

Thank you for your reply. I feel like a 5 year old in a class for advanced quantum physics. I understand the declaring of variables.

Please Login or Register  to view this content.

would you use this for all code written or only when there are variables?

Please Login or Register  to view this content.

Do I change it to

Please Login or Register  to view this content.

?
I am still going through the code to learn. Very gratefull with all the commenting within which is very handy

[LJMetzger]

(Option Explicit) would you use this for all code written or only when there are variables?

It is good programming practice to use 'Option Explicit (only once at the top of each module (e.g. Module1, Module2, etc)) all the time, whether there are variables present or not.

Do I change it to
ThisWorkbook.Path & "\Archive"

Close. You probably need a seprator between your subfolder name and your file name. I would try:

Please Login or Register  to view this content.

Here are a couple of more tips that may help you:
In Excel VBA, change the name of Module1 or any module to something that means something to you as follows:
a. Click on any cell in the Excel Spreadsheet.
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. F4 to get the 'Properties' Window.
e. Left Click on the module you want to rename in the 'Project Explorer'.
f. In the Properties Window, next to '(Name)', change the name as required.

Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

Lewis

[realrookie]

Hello again and thank you for your response. I am having a few issues with your code.

When I open your file and click the macro, it will save a new file with -001 in the end, as I requested. Unfortunately it does not retain any data entered in the original file and executing the macro again gives me your error message.
When I change the path with a separator e.g "\Folder\" then I get the message your msgbox with the error in it.
When I open up the file with the extension -001 and execute the macro, it will say it saved but no file is present. (scanned pc for it but nothing is there).
Removing Application.Quit, causes the file to say it is saved but doesn't. It renames the file in use.

Please do not be offended, maybe this code is a step to much above my head (guarantee it is) but I am intrigued by it.

I replaced your code with

Please Login or Register  to view this content.

to get something working (Option Explicit does not allow this macro to run) which sort does what I want, but I would have preferred the ascending numbers instead a stamp.

As I am learning I will need to step back a little and take your solution a bit slower. I prefer to run before I can walk, which is not helping in this case

[LJMetzger]

Hi,

I apologize for the problems you were having, especially since you gave me rep points (which I thank you for), but are not deserved yet. My original (sequential) code was not meant to write to a SubFolder, which was probably the cause of some if not all of your problems.

Your code with the timestamp is the way I usually backup files.

Try out the code in the attached file (code follows) which can:
a. Save as sequential backups to SubFolder 'Archive'.
b. Save as timestamp backups to SubFolder 'Archive'.
c. Display the 'Archive' SubFolder in Windows Explorer.

Please note that your code is not really a backup, but a snapshot, as the original file was not saved. My code above saves the current file before creating the backup copy.

I made several of the routines 'Private' to not interfere with my previous code, if it exists in the same file. If 2 public routines have the same name in an Excel File, Excel will lockup and the file will appear corrupt.

In an ordinary code module:

Please Login or Register  to view this content.

I apologize again for my previous errors.

Lewis