Ok so I thought I had everything sorted with the spreadsheet I was making
until my line manager thought it would be a great idea to have a spreadsheet
that would archive itself at the end of the month and make a new version I
guess through a template for the new month. But whilst doing so use the last
values from the old spreadsheet to use as the first ones in the new
spreadsheet.

I'm now completely lost. Oh and they need to name themselves after the month
and year.

Any suggestions???

Use File | Save As
--
Jim Cone
Portland, Oregon USA



"Nathanael"

wrote in message
Ok so I thought I had everything sorted with the spreadsheet I was making
until my line manager thought it would be a great idea to have a spreadsheet
that would archive itself at the end of the month and make a new version I
guess through a template for the new month. But whilst doing so use the last
values from the old spreadsheet to use as the first ones in the new
spreadsheet.
I'm now completely lost. Oh and they need to name themselves after the month
and year.

Any suggestions???

Jim Cone has actually given what is probably the easiest and safest way to do
it, simply use File | Save As and give it a new name.

Now, if you really want it to be automated, here's some VBA code to stick
into the Workbook's Open event (if you have questions on how to get to the
right place for the code, just ask) that will do it for you automatically.
Hopefully the code is commented enough so you can see what's going on, but to
recap: the first time you open a file in a new month, could be on the 1st or
maybe later in the month, as on the 30th!, it checks the date to see if
YEAR/Month has changed to a later date. If it has, then th existing file is
saved, but with an "a" stuck in the filename so that if you were to open that
"archived" file sometime down the road, it won't get renamed again. After
saving with the archive name, it kills the original file off, and gives
itself a new name reflecting the current year/month.

As noted in the comments, the filename MUST take on the form of some
phrase/name that ends with _YYYYMM.xls, where YYYY is a 4-digit year, and MM
is a 2-digit month. So for testing, you can start off with a name like
TestAutoRename_200812.xls and once the code is in it, and you save, close
and open it, you should find that there's a TestAutoRename_200812a.xls in the
folder, that TestAutoRename_200812.xls no longer exists in it, and the file
you're working with has suddenly become named TestAutoRename_200901.xls

Here's the code:

Private Sub Workbook_Open()
'assumes naming convention like
' phrase, underscore, YYYYMM, i.e.
' myWorkbook_200901.xls
'AND that when a workbook is
'auto-archived, that the filename has
'an "a" following the month portion of the name, as
' myWorkbook_200901a.xls
'
Dim currentWBName As String
Dim newWBName As String
Dim fileToKill As String
Dim oldYear As Integer
Dim oldMonth As Integer

'is this an 'archive' workbook, if so, do nothing
If Mid(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, _
".") - 1, 1) = "a" Then
Exit Sub
End If

oldYear = Mid(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, "_") + 1, 4)
oldMonth = Mid(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, "_") + 5, 2)
If (oldYear < Year(Now())) Or _
(oldYear = Year(Now()) And oldMonth < Month(Now())) Then
'oldYear < Year(Now()) tests if we've gone from Dec to Jan
'old Year = Year(Now()) and oldMonth<Month(now()) tests
'if we have moved to a new month in the current year
fileToKill = ThisWorkbook.FullName
newWBName = Left(fileToKill, _
InStrRev(fileToKill, ".") - 1)
newWBName = newWBName & "a."
newWBName = newWBName & Right(fileToKill, _
Len(fileToKill) - InStrRev(fileToKill, "."))
'this workbook gets saved with the "a" added to the name
ThisWorkbook.SaveAs newWBName
'the old copy, without the "a" gets killed/Deleted
Kill fileToKill
'now give it a new name based on current YEAR/Month
currentWBName = Left(newWBName, _
InStrRev(newWBName, "_")) & Trim(Str(Year(Now()))) & _
Format(Month(Now()), "00") & "." & _
Right(newWBName, Len(newWBName) - InStrRev(newWBName, "."))
ThisWorkbook.SaveAs currentWBName
'voila! the workbook has new name needed
End If
End Sub


"Nathanael" wrote:

Ok so I thought I had everything sorted with the spreadsheet I was making
until my line manager thought it would be a great idea to have a spreadsheet
that would archive itself at the end of the month and make a new version I
guess through a template for the new month. But whilst doing so use the last
values from the old spreadsheet to use as the first ones in the new
spreadsheet.

I'm now completely lost. Oh and they need to name themselves after the month
and year.

Any suggestions???

Many thanks for your help. I've put the code in place and it works perfectly.
The only thing I need it to do now is when a new month starts all the old
values are deleted except for the totals which need to be used at the
beginning on the new months sheet.
Any help on that would be great.

Cheers
Nathanael

"JLatham" wrote:

Jim Cone has actually given what is probably the easiest and safest way to do
it, simply use File | Save As and give it a new name.

Now, if you really want it to be automated, here's some VBA code to stick
into the Workbook's Open event (if you have questions on how to get to the
right place for the code, just ask) that will do it for you automatically.
Hopefully the code is commented enough so you can see what's going on, but to
recap: the first time you open a file in a new month, could be on the 1st or
maybe later in the month, as on the 30th!, it checks the date to see if
YEAR/Month has changed to a later date. If it has, then th existing file is
saved, but with an "a" stuck in the filename so that if you were to open that
"archived" file sometime down the road, it won't get renamed again. After
saving with the archive name, it kills the original file off, and gives
itself a new name reflecting the current year/month.

As noted in the comments, the filename MUST take on the form of some
phrase/name that ends with _YYYYMM.xls, where YYYY is a 4-digit year, and MM
is a 2-digit month. So for testing, you can start off with a name like
TestAutoRename_200812.xls and once the code is in it, and you save, close
and open it, you should find that there's a TestAutoRename_200812a.xls in the
folder, that TestAutoRename_200812.xls no longer exists in it, and the file
you're working with has suddenly become named TestAutoRename_200901.xls

Here's the code:

Private Sub Workbook_Open()
'assumes naming convention like
' phrase, underscore, YYYYMM, i.e.
' myWorkbook_200901.xls
'AND that when a workbook is
'auto-archived, that the filename has
'an "a" following the month portion of the name, as
' myWorkbook_200901a.xls
'
Dim currentWBName As String
Dim newWBName As String
Dim fileToKill As String
Dim oldYear As Integer
Dim oldMonth As Integer

'is this an 'archive' workbook, if so, do nothing
If Mid(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, _
".") - 1, 1) = "a" Then
Exit Sub
End If

oldYear = Mid(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, "_") + 1, 4)
oldMonth = Mid(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, "_") + 5, 2)
If (oldYear < Year(Now())) Or _
(oldYear = Year(Now()) And oldMonth < Month(Now())) Then
'oldYear < Year(Now()) tests if we've gone from Dec to Jan
'old Year = Year(Now()) and oldMonth<Month(now()) tests
'if we have moved to a new month in the current year
fileToKill = ThisWorkbook.FullName
newWBName = Left(fileToKill, _
InStrRev(fileToKill, ".") - 1)
newWBName = newWBName & "a."
newWBName = newWBName & Right(fileToKill, _
Len(fileToKill) - InStrRev(fileToKill, "."))
'this workbook gets saved with the "a" added to the name
ThisWorkbook.SaveAs newWBName
'the old copy, without the "a" gets killed/Deleted
Kill fileToKill
'now give it a new name based on current YEAR/Month
currentWBName = Left(newWBName, _
InStrRev(newWBName, "_")) & Trim(Str(Year(Now()))) & _
Format(Month(Now()), "00") & "." & _
Right(newWBName, Len(newWBName) - InStrRev(newWBName, "."))
ThisWorkbook.SaveAs currentWBName
'voila! the workbook has new name needed
End If
End Sub


"Nathanael" wrote:

Ok so I thought I had everything sorted with the spreadsheet I was making
until my line manager thought it would be a great idea to have a spreadsheet
that would archive itself at the end of the month and make a new version I
guess through a template for the new month. But whilst doing so use the last
values from the old spreadsheet to use as the first ones in the new
spreadsheet.

I'm now completely lost. Oh and they need to name themselves after the month
and year.

Any suggestions???

For that kind of thing I'd probably need a copy of the workbook with
explanation as to exactly which cells need to be cleared out and which ones
need to be kept. I can give it a go if you want if you'll email a sample end
of month workbook to (remove spaces)
Help From @ JLathamSite .com


"Nathanael" wrote:

Many thanks for your help. I've put the code in place and it works perfectly.
The only thing I need it to do now is when a new month starts all the old
values are deleted except for the totals which need to be used at the
beginning on the new months sheet.
Any help on that would be great.

Cheers
Nathanael

"JLatham" wrote:

Jim Cone has actually given what is probably the easiest and safest way to do
it, simply use File | Save As and give it a new name.

Now, if you really want it to be automated, here's some VBA code to stick
into the Workbook's Open event (if you have questions on how to get to the
right place for the code, just ask) that will do it for you automatically.
Hopefully the code is commented enough so you can see what's going on, but to
recap: the first time you open a file in a new month, could be on the 1st or
maybe later in the month, as on the 30th!, it checks the date to see if
YEAR/Month has changed to a later date. If it has, then th existing file is
saved, but with an "a" stuck in the filename so that if you were to open that
"archived" file sometime down the road, it won't get renamed again. After
saving with the archive name, it kills the original file off, and gives
itself a new name reflecting the current year/month.

As noted in the comments, the filename MUST take on the form of some
phrase/name that ends with _YYYYMM.xls, where YYYY is a 4-digit year, and MM
is a 2-digit month. So for testing, you can start off with a name like
TestAutoRename_200812.xls and once the code is in it, and you save, close
and open it, you should find that there's a TestAutoRename_200812a.xls in the
folder, that TestAutoRename_200812.xls no longer exists in it, and the file
you're working with has suddenly become named TestAutoRename_200901.xls

Here's the code:

Private Sub Workbook_Open()
'assumes naming convention like
' phrase, underscore, YYYYMM, i.e.
' myWorkbook_200901.xls
'AND that when a workbook is
'auto-archived, that the filename has
'an "a" following the month portion of the name, as
' myWorkbook_200901a.xls
'
Dim currentWBName As String
Dim newWBName As String
Dim fileToKill As String
Dim oldYear As Integer
Dim oldMonth As Integer

'is this an 'archive' workbook, if so, do nothing
If Mid(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, _
".") - 1, 1) = "a" Then
Exit Sub
End If

oldYear = Mid(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, "_") + 1, 4)
oldMonth = Mid(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, "_") + 5, 2)
If (oldYear < Year(Now())) Or _
(oldYear = Year(Now()) And oldMonth < Month(Now())) Then
'oldYear < Year(Now()) tests if we've gone from Dec to Jan
'old Year = Year(Now()) and oldMonth<Month(now()) tests
'if we have moved to a new month in the current year
fileToKill = ThisWorkbook.FullName
newWBName = Left(fileToKill, _
InStrRev(fileToKill, ".") - 1)
newWBName = newWBName & "a."
newWBName = newWBName & Right(fileToKill, _
Len(fileToKill) - InStrRev(fileToKill, "."))
'this workbook gets saved with the "a" added to the name
ThisWorkbook.SaveAs newWBName
'the old copy, without the "a" gets killed/Deleted
Kill fileToKill
'now give it a new name based on current YEAR/Month
currentWBName = Left(newWBName, _
InStrRev(newWBName, "_")) & Trim(Str(Year(Now()))) & _
Format(Month(Now()), "00") & "." & _
Right(newWBName, Len(newWBName) - InStrRev(newWBName, "."))
ThisWorkbook.SaveAs currentWBName
'voila! the workbook has new name needed
End If
End Sub


"Nathanael" wrote:

Ok so I thought I had everything sorted with the spreadsheet I was making
until my line manager thought it would be a great idea to have a spreadsheet
that would archive itself at the end of the month and make a new version I
guess through a template for the new month. But whilst doing so use the last
values from the old spreadsheet to use as the first ones in the new
spreadsheet.

I'm now completely lost. Oh and they need to name themselves after the month
and year.

Any suggestions???

Thanks for you kind offer. A sample should be heading its way to you right now.

Many thanks again for your help
Nathanael

"JLatham" wrote:

For that kind of thing I'd probably need a copy of the workbook with
explanation as to exactly which cells need to be cleared out and which ones
need to be kept. I can give it a go if you want if you'll email a sample end
of month workbook to (remove spaces)
Help From @ JLathamSite .com


"Nathanael" wrote:

Many thanks for your help. I've put the code in place and it works perfectly.
The only thing I need it to do now is when a new month starts all the old
values are deleted except for the totals which need to be used at the
beginning on the new months sheet.
Any help on that would be great.

Cheers
Nathanael

"JLatham" wrote:

Jim Cone has actually given what is probably the easiest and safest way to do
it, simply use File | Save As and give it a new name.

Now, if you really want it to be automated, here's some VBA code to stick
into the Workbook's Open event (if you have questions on how to get to the
right place for the code, just ask) that will do it for you automatically.
Hopefully the code is commented enough so you can see what's going on, but to
recap: the first time you open a file in a new month, could be on the 1st or
maybe later in the month, as on the 30th!, it checks the date to see if
YEAR/Month has changed to a later date. If it has, then th existing file is
saved, but with an "a" stuck in the filename so that if you were to open that
"archived" file sometime down the road, it won't get renamed again. After
saving with the archive name, it kills the original file off, and gives
itself a new name reflecting the current year/month.

As noted in the comments, the filename MUST take on the form of some
phrase/name that ends with _YYYYMM.xls, where YYYY is a 4-digit year, and MM
is a 2-digit month. So for testing, you can start off with a name like
TestAutoRename_200812.xls and once the code is in it, and you save, close
and open it, you should find that there's a TestAutoRename_200812a.xls in the
folder, that TestAutoRename_200812.xls no longer exists in it, and the file
you're working with has suddenly become named TestAutoRename_200901.xls

Here's the code:

Private Sub Workbook_Open()
'assumes naming convention like
' phrase, underscore, YYYYMM, i.e.
' myWorkbook_200901.xls
'AND that when a workbook is
'auto-archived, that the filename has
'an "a" following the month portion of the name, as
' myWorkbook_200901a.xls
'
Dim currentWBName As String
Dim newWBName As String
Dim fileToKill As String
Dim oldYear As Integer
Dim oldMonth As Integer

'is this an 'archive' workbook, if so, do nothing
If Mid(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, _
".") - 1, 1) = "a" Then
Exit Sub
End If

oldYear = Mid(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, "_") + 1, 4)
oldMonth = Mid(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, "_") + 5, 2)
If (oldYear < Year(Now())) Or _
(oldYear = Year(Now()) And oldMonth < Month(Now())) Then
'oldYear < Year(Now()) tests if we've gone from Dec to Jan
'old Year = Year(Now()) and oldMonth<Month(now()) tests
'if we have moved to a new month in the current year
fileToKill = ThisWorkbook.FullName
newWBName = Left(fileToKill, _
InStrRev(fileToKill, ".") - 1)
newWBName = newWBName & "a."
newWBName = newWBName & Right(fileToKill, _
Len(fileToKill) - InStrRev(fileToKill, "."))
'this workbook gets saved with the "a" added to the name
ThisWorkbook.SaveAs newWBName
'the old copy, without the "a" gets killed/Deleted
Kill fileToKill
'now give it a new name based on current YEAR/Month
currentWBName = Left(newWBName, _
InStrRev(newWBName, "_")) & Trim(Str(Year(Now()))) & _
Format(Month(Now()), "00") & "." & _
Right(newWBName, Len(newWBName) - InStrRev(newWBName, "."))
ThisWorkbook.SaveAs currentWBName
'voila! the workbook has new name needed
End If
End Sub


"Nathanael" wrote:

Ok so I thought I had everything sorted with the spreadsheet I was making
until my line manager thought it would be a great idea to have a spreadsheet
that would archive itself at the end of the month and make a new version I
guess through a template for the new month. But whilst doing so use the last
values from the old spreadsheet to use as the first ones in the new
spreadsheet.

I'm now completely lost. Oh and they need to name themselves after the month
and year.

Any suggestions???

Email and file received, return email sent to you.

"Nathanael" wrote:

Thanks for you kind offer. A sample should be heading its way to you right now.

Many thanks again for your help
Nathanael

"JLatham" wrote:

For that kind of thing I'd probably need a copy of the workbook with
explanation as to exactly which cells need to be cleared out and which ones
need to be kept. I can give it a go if you want if you'll email a sample end
of month workbook to (remove spaces)
Help From @ JLathamSite .com


"Nathanael" wrote:

Many thanks for your help. I've put the code in place and it works perfectly.
The only thing I need it to do now is when a new month starts all the old
values are deleted except for the totals which need to be used at the
beginning on the new months sheet.
Any help on that would be great.

Cheers
Nathanael

"JLatham" wrote:

Jim Cone has actually given what is probably the easiest and safest way to do
it, simply use File | Save As and give it a new name.

Now, if you really want it to be automated, here's some VBA code to stick
into the Workbook's Open event (if you have questions on how to get to the
right place for the code, just ask) that will do it for you automatically.
Hopefully the code is commented enough so you can see what's going on, but to
recap: the first time you open a file in a new month, could be on the 1st or
maybe later in the month, as on the 30th!, it checks the date to see if
YEAR/Month has changed to a later date. If it has, then th existing file is
saved, but with an "a" stuck in the filename so that if you were to open that
"archived" file sometime down the road, it won't get renamed again. After
saving with the archive name, it kills the original file off, and gives
itself a new name reflecting the current year/month.

As noted in the comments, the filename MUST take on the form of some
phrase/name that ends with _YYYYMM.xls, where YYYY is a 4-digit year, and MM
is a 2-digit month. So for testing, you can start off with a name like
TestAutoRename_200812.xls and once the code is in it, and you save, close
and open it, you should find that there's a TestAutoRename_200812a.xls in the
folder, that TestAutoRename_200812.xls no longer exists in it, and the file
you're working with has suddenly become named TestAutoRename_200901.xls

Here's the code:

Private Sub Workbook_Open()
'assumes naming convention like
' phrase, underscore, YYYYMM, i.e.
' myWorkbook_200901.xls
'AND that when a workbook is
'auto-archived, that the filename has
'an "a" following the month portion of the name, as
' myWorkbook_200901a.xls
'
Dim currentWBName As String
Dim newWBName As String
Dim fileToKill As String
Dim oldYear As Integer
Dim oldMonth As Integer

'is this an 'archive' workbook, if so, do nothing
If Mid(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, _
".") - 1, 1) = "a" Then
Exit Sub
End If

oldYear = Mid(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, "_") + 1, 4)
oldMonth = Mid(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, "_") + 5, 2)
If (oldYear < Year(Now())) Or _
(oldYear = Year(Now()) And oldMonth < Month(Now())) Then
'oldYear < Year(Now()) tests if we've gone from Dec to Jan
'old Year = Year(Now()) and oldMonth<Month(now()) tests
'if we have moved to a new month in the current year
fileToKill = ThisWorkbook.FullName
newWBName = Left(fileToKill, _
InStrRev(fileToKill, ".") - 1)
newWBName = newWBName & "a."
newWBName = newWBName & Right(fileToKill, _
Len(fileToKill) - InStrRev(fileToKill, "."))
'this workbook gets saved with the "a" added to the name
ThisWorkbook.SaveAs newWBName
'the old copy, without the "a" gets killed/Deleted
Kill fileToKill
'now give it a new name based on current YEAR/Month
currentWBName = Left(newWBName, _
InStrRev(newWBName, "_")) & Trim(Str(Year(Now()))) & _
Format(Month(Now()), "00") & "." & _
Right(newWBName, Len(newWBName) - InStrRev(newWBName, "."))
ThisWorkbook.SaveAs currentWBName
'voila! the workbook has new name needed
End If
End Sub


"Nathanael" wrote:

Ok so I thought I had everything sorted with the spreadsheet I was making
until my line manager thought it would be a great idea to have a spreadsheet
that would archive itself at the end of the month and make a new version I
guess through a template for the new month. But whilst doing so use the last
values from the old spreadsheet to use as the first ones in the new
spreadsheet.

I'm now completely lost. Oh and they need to name themselves after the month
and year.

Any suggestions???