How to hide rows for specific months

chward · August 1st, 2010, 01:10 PM

Below is my code for my timesheet. I want to be able to hide rows 111 to 116 for months that have less than 31 days (months 4,6,9,11) when i click the command button. Thank you for any help!

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("Template").Visible = True

Worksheets("Template").Copy Before:=Worksheets("Template")

temp = ActiveSheet.Index - 1

oldmonth = Month(Worksheets(temp).Range("D2"))
oldDay = Day(Worksheets(temp).Range("D2"))

If oldDay = 1 Then
'new starting date
newday = 16
lastday = endofmonth(oldmonth)
newmonth = oldmonth

'new ending date

Else
newday = 1
lastday = 15

If oldmonth = 12 Then
newmonth = 1
Else
newmonth = oldmonth + 1
End If
End If

'Rename current Sheet
newsheetname = newmonth & "-" & newday & " to " & newmonth & "-" & lastday

ActiveSheet.Name = newsheetname

ActiveSheet.Range("D2") = newmonth & "/" & newday
' ActiveSheet.Index

'If (Year(Worksheets(temp).Range("s2")) < Year(Worksheets(temp).Range("s2") + 13)) Then
' Worksheets.Add
' ActiveSheet.Name = Year(Worksheets(temp).Range("q2") + 13)
' If Application.Version > 10 Then
' ActiveSheet.Tab.ColorIndex = 3
' End If
' Worksheets(newsheetname).Activate
'End If

Sheets("Template").Visible = False

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Module:
Public Function endofmonth(monthA)

Select Case monthA
Case 1, 3, 5, 7, 8, 10, 12
monthend = 31
Case 4, 6, 9, 11
monthend = 30
Case Else
monthend = 28
End Select

If monthA = 2 Then
If IsLeapYear(Year(Now())) = True Then
monthend = 29
Else
monthend = 28
End If
End If

endofmonth = monthend

End Function

Public Function nextmonth(Month)

End Function

Public Function IsLeapYear(Y As Integer)
IsLeapYear = Month(DateSerial(Y, 2, 29)) = 2
End Function

Shasur · August 3rd, 2010, 02:46 AM

Hi

Do you have any dates in 111 to 116; for eg, D111 etc?

Then you can get the Date part and check

Cheers
Shasur

allenm · August 18th, 2010, 04:34 PM

Interesting way to accommodate for date data types.

This is how I handled it:

Create a module and place the actual functionality below there. no point in having replicated data due to template copying.

Code:

Public Sub CreateNextTimesheet()

'Creates next Timesheet from Template
  Dim wsPrevTimesheet As Worksheet, wsNewTimesheet As Worksheet, wsTemplate As Worksheet
  Dim dDateFrom As Date, dDateTo As Date, sBuildDate As String, iMonth As Long, iYear As Long
  Dim iLastDay As Long

'Create pointer to worksheets and add new one to list
  Set wsTemplate = ActiveWorkbook.Worksheets("Template")
  Set wsPrevTimesheet = ActiveWorkbook.Worksheets(wsTemplate.Index - 1)
  wsTemplate.Visible = xlSheetVisible
  wsTemplate.Copy Before:=wsTemplate
  Set wsNewTimesheet = ActiveWorkbook.ActiveSheet

'Copy over the template with button.  This way only 1 line of code is replicated.
  wsTemplate.Visible = xlSheetHidden
  dDateFrom = CDate(wsPrevTimesheet.Range("D2").Value) 'Use full date with year
  iMonth = Month(dDateFrom)
  iYear = Year(dDateFrom)

'Create Timesheet 1st to 15th
  If Day(dDateFrom) = 1 Then
    dDateFrom = dDateFrom + 15
    iMonth = iMonth + 1
    If iMonth > 12 Then
      iMonth = 1
      iYear = iYear + 1
    End If
    dDateTo = CDate(iMonth & "/01/" & iYear) - 1 'Subtract back 1 day, make Excel Date type do the work

'Create Timesheet 16th to end of month
  Else
    iMonth = iMonth + 1
    If iMonth > 12 Then
      iMonth = 1
      iYear = iYear + 1
    End If
    dDateFrom = CDate(iMonth & "/01/" & iYear)
    dDateTo = CDate(iMonth & "/15/" & iYear)
  End If

'Get last day of month making Excel Date type do the work again.
'Formula starts with 25th of month, adds 9 days, then subtracts
'day of month which ends at last day of previous month.
  iLastDay = Day(CDate(Month(dDateTo) & "/25/" & Year(dDateTo)) + 9 _
    - Day(CDate(Month(dDateTo) & "/25/" & Year(dDateTo)) + 9))

'Set new timesheet values and make it the active sheet
  With wsNewTimesheet
    .Activate
    .Name = Format(dDateFrom, "MM-DD-YYYY") & " to " & Format(dDateTo, "MM-DD-YYYY")
    .Range("D2").Value = dDateFrom
    If iLastDay < 31 Then .Range("111:116").EntireRow.Hidden = True
  End With

End Sub

Then on the template sheet your button code would be:

Code:

Private Sub NextTimeSheet_Button_Click()

  Call CreateNextTimesheet

End Sub

This may not be exactly what you want, but hopefully it points you in the right direction.