how to create sheets only for weekdays using coding in 2003

[vlavan08]

Hi frds,

I have a doubt and can anyone clarify it.

Based on the template, i want to create separate sheets for every mon to fri (not for weekends)present in the respective month

Example:for december month.

If i press over a button which is the tempplate sheet, i should get
1st sheet name as 01.12.2014 - 05.12.2014
2nd sheet name as 08.12.2014-12.12.2014
3rd sheet name as 15.12.2014-19.12.2014
4th sheet name as 22.12.2014-26.12.2014
5th sheet name as 29.12.2014-31.12.2014

I tried. but i couldn .. can any one help me on this

 

[NARAYANK991]

Hi ,

See if this works :

Private Sub temp()
            CurrMonth = VBA.Month(Date)
            CurrYear = VBA.Year(Date)
            StartoftheMonth = DateSerial(CurrYear, CurrMonth, 1)
            Do
              Do While VBA.Weekday(StartoftheMonth) = vbSaturday Or VBA.Weekday(StartoftheMonth) = vbSunday
                  StartoftheMonth = StartoftheMonth + 1
              Loop
              FromStr = Format(StartoftheMonth, "dd.mm.yyyy")
              EndoftheWeek = StartoftheMonth
              Do Until VBA.Weekday(EndoftheWeek) = vbFriday Or Month(EndoftheWeek) <> CurrMonth
                  EndoftheWeek = EndoftheWeek + 1
              Loop
              If Month(EndoftheWeek) <> CurrMonth Then EndoftheWeek = EndoftheWeek - 1
              ToStr = Format(EndoftheWeek, "dd.mm.yyyy")
              ShtName = FromStr & " - " & ToStr
              Worksheets.Add
              ActiveSheet.Name = ShtName
              StartoftheMonth = EndoftheWeek + 1
            Loop Until Month(StartoftheMonth) <> CurrMonth
End Sub

I have no idea whether the functions used are available in 2003.

Narayan

 

[vlavan08]

Hi ,

See if this works :

Private Sub temp()
            CurrMonth = VBA.Month(Date)
            CurrYear = VBA.Year(Date)
            StartoftheMonth = DateSerial(CurrYear, CurrMonth, 1)
            Do
              Do While VBA.Weekday(StartoftheMonth) = vbSaturday Or VBA.Weekday(StartoftheMonth) = vbSunday
                  StartoftheMonth = StartoftheMonth + 1
              Loop
              FromStr = Format(StartoftheMonth, "dd.mm.yyyy")
              EndoftheWeek = StartoftheMonth
              Do Until VBA.Weekday(EndoftheWeek) = vbFriday Or Month(EndoftheWeek) <> CurrMonth
                  EndoftheWeek = EndoftheWeek + 1
              Loop
              If Month(EndoftheWeek) <> CurrMonth Then EndoftheWeek = EndoftheWeek - 1
              ToStr = Format(EndoftheWeek, "dd.mm.yyyy")
              ShtName = FromStr & " - " & ToStr
              Worksheets.Add
              ActiveSheet.Name = ShtName
              StartoftheMonth = EndoftheWeek + 1
            Loop Until Month(StartoftheMonth) <> CurrMonth
End Sub

I have no idea whether the functions used are available in 2003.

Narayan

Thanks u...