CODEWITHSUNDEEP
excelexport-to-excelvba
Lrn
Lrn

Reputation: 327

Macro to show/hide a worksheet based on date value

I have an excel workbook created by an executable with data for days of the month on separate worksheets. 'Sheet 1' of the executable also has the days of the month listed. I would like to write a macro that will show/hide the worksheets based on the date in 'Sheet 1'.

For Instance, if the data for the month of Jan has days 1,2,3,4,5,11,12 displayed then the macro should show only the corresponding worksheets for Day1, Day2, Day3, Day4,Day5 and hide Day6 through Day10 and show Day11 and Day12. Any pointers are appreciated.

Thank you.

Upvotes: 1

Views: 1713

Answers (1)

Chris Flynn
Chris Flynn

Reputation: 953

public sub setSheetVisiblity()

  'Load the data from sheet 1 into a collection
  'I'm making the assumption that you just have days listed horizontally from 
  '1A to 1*

  Dim currentColumn as Integer
  Dim activeDayCollection as Collection

  currentColumn = 1
  Set activeDayCollection = new Collection

  While Cells(currentColumn, 1).Value <> ""

    activeDayCollection.add Cells(currentColumn, 1).Value 

    currentColumn = currentColumn + 1
  Wend

  'Make every sheet invisible/visible
  For each currentWorksheet as Worksheet in Worksheets

    If currentWorksheet.Name == "Day" + activeDayCollection.Item 1 Then
      currentWorksheet.Visible = true
      activeDayCollection.Remove 1
    Else
       currentWorksheet.Visible = false
    End If

  Next currentWorksheet
end sub

The code works off of the assumption that the days in your first sheet are in increasing order, the sheets are named Day###, where ### is the day number, and you will probably have to add another line to manually unhide your first sheet. I don't have vba with me so this code might have some syntax errors, but it should get you going in the right direction.

Upvotes: 1

Related Questions