Need to save a years worth of notes

ShawnSPS

Board Regular
Joined
Mar 15, 2023
Messages
61
Office Version
  1. 2003 or older
Platform
  1. 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.



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 Sub



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.



 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top