Please Help me on this code as soon as possible

• • Reply

  18 years ago

  by allrounder

  Waqar

  , United States

  Joined 19 years ago

  Basically the file iam about to discuss is use to handle small database of a manufacturing and marketing company , so in one file there are 5 different sheets representing sale , store etc..    
  I have got a VB code for excel which do the following:-    
     
  1) When ever i open that specific file of excel, a new sheet with same formulas and headers appear with the date (when the file is opened) as it name and do copy of of four desired columns.    
     
  2) Now this code is only for creating a new sheet in the same file with day  , date and desired interchanging.    
   Now as my file contains more than one sheet so i need to copy the same file again to use it on the next day.    
     
     
  3) i want to use the same code or with some modification in it so that a new file opens with the same data and same fucntions. Only thing i need is that a new file opens instead of sheet , please do the required changes in the code.    
     
  the Word "Draft" in this code is basically the name of the source sheet.    
     
  Option Explicit    
     
  Public Sub auto_open()    
     
  Dim newSheet As Worksheet    
  Dim yestSheet As Worksheet    
  Dim temp As Date    
  Dim tempDate As Date    
  On Error Resume Next    
     yestSheet = ActiveWorkbook.Worksheets(Format$(Date, "yyyy-mm-dd"))    
     If (Err.Number = 0) Then    
         Exit Sub    
     End If    
     
     tempDate = 0    
     On Error Resume Next    
     For Each yestSheet In ActiveWorkbook.Worksheets    
         temp = CDate(yestSheet.Name)    
         If (Err.Number = 0) Then    
             If (tempDate < temp) Then    
                 tempDate = temp    
             End If    
         End If    
         Err.Clear    
     Next yestSheet    
     On Error GoTo 0    
     Set yestSheet = ActiveWorkbook.Worksheets("Draft")    
     Set newSheet = ActiveWorkbook.Worksheets.Add(yestSheet)    
     yestSheet.Cells.Copy newSheet.Cells    
     newSheet.Name = Format$(Date, "yyyy-mm-dd")    
     If (tempDate > 0) Then    
         Set yestSheet = ActiveWorkbook.Worksheets(Format$(tempDate, "yyyy-mm-dd"))    
         yestSheet.Range("E9:E28").Copy    
         newSheet.Range("B9:B28").PasteSpecial xlPasteValues    
         yestSheet.Range("K9:K28").Copy    
         newSheet.Range("H9:H28").PasteSpecial xlPasteValues    
     End If    
     newSheet.Range("C1").Value = Format(Date, "Short Date")    
     newSheet.Range("E1").Value = Format$(Date, "dddd")    
     Set newSheet = Nothing    
     Set yestSheet = Nothing    
  End Sub    
  

  Report abuse