Increment File Name by 1

[Blue]

I have 52 files, one for each week of the year, and one blank master. When
a change is required the master is updated then the files are over written
for the weeks to come, this is done manually one file at a time. The file
names are
RC1 WK1
RC1 WK2
etc
Is it possible to save the blank master as RC1 WK? to RC1 WK52, ? being the
first file to start from, over writing the previous files?

Thanks Blue

[Leith Ross]

Hello Blue,

If I understood your post correctly, when the master is updated the replaced file is the same as the master, only the name is different. You also only update from the current week through the end of the year. For example, the master is updated in week 25 then weeks 25 - 52 are updated (replaced by the master file). Also, the other workbooks are assumed to be in the same folder as the master. Here is a macro to help you do that. For safety it will ask you if you want to replace the file. Since I don't know your skill level, I have included step by step instructions on loading and running the macro.
_________________________________________________________________

Public Sub UpdateWorkbooks()

Dim Ret
Dim I As Integer
Dim W As Integer
Dim InputMsg As String
Dim WeekStr As String
Dim WkbName As String

On Error GoTo Fault

'Ask for the Week Number
InputMsg = "Enter the Week Number to start with below." & vbCrLf _
&"To Exit this Macro, Click Cancel or leave the entry below blank and Click OK."

WeekStr = InputBox(InputMsg, "Update Workbooks")

If WeekStr = "" Then Exit Sub

W = Val(WeekStr)

'Update Workbooks Loop
For I = W to 52

'Save this Workbook as the New Workbook - Asks to Replace the File
WkbName = ThisWorkbook.Path & "\TEMP WK" & I & ".xls"
ThisWorkbook.SaveAs Filename:= WkbName, FileFormat:= xlWorkbookNormal

Next I

Fault:
Msg = "An Error has occurred. This macro will now Terminate." & vbCrLf _
& "Error Number = " & Err.Number & vbCrLf _
& "Message: " & Err.Description

Ret = MsgBox(Msg, vbOkonly + vbCritical, "Update Workbooks Macro")
Err = 0

End Sub
_________________________________________________________________

Installing the Macro:

1) Left Click to left of the word "Public" below the first line and hold the Left mouse button down and move to the bottom line and Release the mouse button. The text should be highlighted.
2) Copy the code by pressing both the Ctrl and C keys together.
3) Open your Workbook
4) Press both the Alt and F11 keys together. This brings up the VB Editor.
5) Press both the Alt and I keys together. Activates the Insert Menu.
6) Press the M key. Inserts a Module into the Project.
7) Paste the code by pressing the Ctrl and V keys together. Pastes in the code.
8) Save the changes by pressing the Ctrl and S keys together.

Running the Macro:

Press the Alt and F8 keys together. This will display the Macro List Select "UpdateWorkbooks" and either press the Enter key or Click OK.

If you have any questions, please contact me here at the forum or by e-mail [email protected]

Hope this helps,
Leith Ross

[Kaisies - ExcelForums.com]

doing from memory, so syntax might not be perfect but....

Private CommandButton1_Click ()

dim x
dim pathofsavefile as string
pathofsavefile = "C:\temp\RC1 WK"

x = 1

application.displayalerts = false
Do Until x > 52
ActiveWorkbook.SaveAs Filename:=pathofsavefile & x
x = x +1
Loop
Application.DisplayAlerts = True

End Sub

[Kaisies - ExcelForums.com]

doing from memory, so syntax might not be perfect but....

Private CommandButton1_Click ()

dim x
dim pathofsavefile as string
pathofsavefile = "C:\temp\RC1 WK"

x = 1

application.displayalerts = false
Do Until x > 52
ActiveWorkbook.SaveAs Filename:=pathofsavefile & x
x = x +1
Loop
Application.DisplayAlerts = True

End Sub

[Soltisolti]

A bit late but thanks for the info, using suggestion from Leith Ross

blue