hiI'm trying to create my first visual basic program in excel I'm trying to create a timesheet that will replicate it's self each year so I don't have to make the changes myselfanyway this is my code below it's not running for some reasonthis is in the workbookPrivate Sub OnTime1()'set timesheet to activate code in main'calls replication produre'date numbers to replicate onApplication.OnTime 39904, DateValue, 73141Dim x As Integerx = 365'365 days in a yearDim startdateto As IntegerFor startdateto = 39904 To 73141startdateto 39904 + xNext'trying to add 365 days till next run dateCall mainDo Until startdateto >= 73141If startdateto >= 73141 Then Exit DoEnd Suband the rest is in a moduleSub main()'where all the code is called fromcreate_directorysaveyearly_changes_to_timesheetsave_asEnd SubSub create_directory()'make place for new timesheet'created by james payneDim startdate As IntegerFor startdate = 2009 To 2100startdate 2009 + 1NextDoChDir "G"ChDir ("timesheet startdate")Range("Parent_path").Valuestartdate = Range("start_variable").Valueenddate = Range("end_varibale").ValueRange("variable_part").Value = startdateFor datebegin = startdate To enddateMkdir_Range("new_directory").ValueRange("variable_part").Value = Range("variable_part").Value + 1NextEnd SubSub save()'save master copy so the year_changes_to_timesheet are saved before preforming the next yearly_changes_to_timesheet sub'created by james payneActiveWorkbook.saveEnd SubSub yearly_changes_to_timesheet()'place to do changes to work sheet it's self'created by james payneDim dayyears As Integerdayyears = 365Dim startdateto As Integerstartdateto1 = 39904For startdateto = 39904 To 73141startdateto1 39904 + dayyearsNextCells(8, 1) = =TEXT(startdateto,"DD/MMMM/YYYY")Sub save_as()'new worksheet producted from master copy (which is updated each year)'created by james payneDim start_workbook_names As IntegerFor start_workbook_names = 2009 To 2100start_workbook_names 2009 + 1NextActiveWorkbook.SaveAs Filename:=timesheetstart_workbook_names.xls"C:\Documents and Settings\Games\Desktop\james documents\excel programming\timesheet start_workbook_names.xls" _, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ReadOnlyRecommended:=False, CreateBackup:=FalseEnd Subany help would be much Apprehended
Hi barca payne. This appears to be a Visual Basic for Applications (VBA) question where you are writing code within Excel itself. Unfortuately this forum is dedicated to VB.NET which is an entirely different thing. please see the sticky thread at the top of this forum (link below) for information to find resources aimed at VBA...
- Marked as answer by Jay_WangMicrosoft employee, Moderator Wednesday, February 11, 2009 3:21 AM