Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro with system clock reference (excel 2000)

    Hi-

    I'm trying to track my expenses in Excel, using it as a check register. When the first of the month rolls around, I would like Excel to refer to the system's clock and copy a particular worksheet, renaming the worksheet with the new month. I know that I need an if/then statement. I just can't figure out the code to make it refer to the clock.

    Any help would be appreciated as I've been pondering this problem for awhile.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro with system clock reference (excel 2000)

    Stephen,

    Day(Now) will return the day number part of the current date

    so use

    If Day(Now) = 1 then
    ..... 'Copy the sheet etc

    Does that help ?

    Andrew C

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro with system clock reference (excel 2000)

    Or using the month's names:

    <pre>Sub test()
    Dim Today As String
    Dim Yesterday As String
    Today = Now()
    Yesterday = Now() - 1
    If Month(Today) <> Month(Yesterday) Then
    ActiveWorkbook.Sheets.Add
    ActiveSheet.Name = MonthName(Month(Today))
    'here I assume that the sheet with the name of the previous month already exists
    ActiveWorkbook.Sheets(MonthName(Month(Yesterday))) .Select
    ActiveSheet.Cells.Copy
    ActiveWorkbook.Sheets(MonthName(Month(Today))).Sel ect
    Range("A1").Select
    ActiveSheet.Paste
    End If
    End Sub
    </pre>


  4. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro with system clock reference (excel 2000)

    Thanks for the code. It works beautifully. I'm now trying to figure out how to move that sheet behind the previous active sheet. For instance, when I ran the code, it placed April in front of March. Is there any kind of Offset property in regards to moving sheets or will I always have to refer to an index number?

    Again, thanks for your help. I had been searching how to do something like that for a long time.

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro with system clock reference (excel 2000)

    Immediately after "ActiveWorkbook.Sheets.Add" add the line:

    ActiveSheet.Move after:=Sheets(MonthName(Month(Yesterday)))

    HTH
    Gre

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Macro with system clock reference (excel 2000)

    This assumes that you will definitely open the spreadsheet on the 1st of each month, even if it is a Holiday or you are ill that day!

    It might be better to generate the current month's sheet name, then check to see if it exists, if so then use it, if not then create it from the previous month's sheet. This will work if you open the workbook on any day where the correct sheet hasn't been created yet.

    StuartR

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro with system clock reference (excel 2000)

    Stuart is right. If you do not run the macro every day (e.g. you don't run it during the weekends) and the first of the month falls on a Saturday or a Sunday, then the new sheet will not be created. I think that in case yesterday and today are belonging to the same month, you should check if the sheet with the month's name already exists. If yes, everything is ok, if not, run the code as if yesterday and today were not belonging to the same month.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro with system clock reference (excel 2000)

    I would do that like this:

    <pre>Sub test()
    Dim strSName As String
    Dim oSheet As Worksheet
    strSName = Format(Now(), "mmmmyyyy")
    On Error Resume Next
    Set oSheet = Worksheets(strSName)
    On Error GoTo 0
    If oSheet Is Nothing Then
    Set oSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
    oSheet.Name = strSName
    End If
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •