Please Help me on this code as soon as possible

  • 18 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    

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Never trust a programmer in a suit.” - Anonymous