ShawnSPS
Board Regular
- Joined
- Mar 15, 2023
- Messages
- 60
- Office Version
- 2003 or older
- Platform
- Windows
Ok the solution I am looking for is beyond my scope of vba knowledge. I need to only have the user hold to a year of notes by using a range, and the amount of notes could vary from year to year. However I have a Archive Marco that copies and saves by a system variable to a for in the users documents area. Any code create will need to make sure that this happens before the no code starts. I need to make they need to be to go back to an archive copy if they need too. The current archive code is below. Any help would be appreciated.
Now because of the update or add on I want the “ Range{b2:d5000”).clear “ would not be used because we want to keep a years worth data. Which would include date, notes and company/client name) I will give you a copy of the new working copy of the workbook. The worksheet is called “Znotes” its important we try to keep the format. I do not have a years worth of notes to give you because it going to vary from year to year.
VBA Code:
Sub archive()
Dim SavePath As String, ArchivePath As String
ActiveSheet.Copy
SavePath = Environ("userprofile") & "\my documents\Archive Notes\zNotes.xls"
ArchivePath = Environ("userprofile") & "\my documents\Archive Notes"
If Len(Dir(ArchivePath, vbDirectory)) = 0 Then
MkDir ArchivePath
End If
Application.DisplayAlerts = False
ActiveSheet.SaveAs SavePath
ActiveWorkbook.Close
Range("b2:d5000").Clear
End SubNow because of the update or add on I want the “ Range{b2:d5000”).clear “ would not be used because we want to keep a years worth data. Which would include date, notes and company/client name) I will give you a copy of the new working copy of the workbook. The worksheet is called “Znotes” its important we try to keep the format. I do not have a years worth of notes to give you because it going to vary from year to year.
