VBA - Maybe Looping - Activate Multiple Sheets?

[ClwnMan76]

First of all, good morning and thanks to all of you who come on here and help us out and never asking much in return. You've inspired me to go to other forums where I actually do something about the subject and help out.

I seem to have a sick dog now that is wanting attention so I've broken down to ask for assistance on a couple things.

As you can see by what I have so far it works, but I know there's gotta be a more efficient way to write and execute this. I have a sheet, 3 letters, for each day of the month. There are 16 sheets in all. I was reading up on cycling through the whole spreadsheet and that's perfectly fine as the sheets that aren't a month won't mess anything up.

Oh, and feel free to correct anything else i have. I'd really like to learn where I can improve!!

I don't know if I can post a second problem, but I was trying to create some code that monitored k2:k22 on the "MAIN" sheet for changes and then would run the code below, but after a few hours I gave up and added a button. LOL See below.

Sub HideUnhide()    
    Application.ScreenUpdating = False
    
    Worksheets("JAN").Activate
    BeginRow = 3
    EndRow = 150
    ChkCol = 35


    For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCol).Value = "hide" Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
    
    Worksheets("FEB").Activate
        BeginRow = 3
    EndRow = 150
    ChkCol = 35


    For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCol).Value = "hide" Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
    
    Application.ScreenUpdating = True


End Sub

I found this code below that I thought I could edit to fit my needs, but no matter what I did it wouldn't work.

Private SubWorksheet_Change (ByVal Target As Range) 
If Not
Intersect (Target, Range ("A1:B100"))
     Is Nothing Then 
 Call Mymacro 
 End If 
 End Sub

I don't know if this will help or not, but here's the file. If you need the password it's in the MAIN sheet cell d28.

P.S. I apologize if I posted incorrectly. I'm a recent convert from another forum that just became way too negative if you weren't perfect.

 

[DanteAmor]

This is a new topic, I suggest you create a new thread, so you will have a chance for more help.

 

[ClwnMan76]

This is a new topic, I suggest you create a new thread, so you will have a chance for more help.

So sorry. YOu're right. Thanks!

 

[DanteAmor]

Youre welcome.

 

[ClwnMan76]

Since the code for the module listed here is in this thread I thought I'd ask.

Is this error because they are using 2013 version and I used 365 to create? I did the compatibility and it didn't show any errors.

Here's the code in Module 1 just in case, but it works on my end.

Sub HideUnhide()  Dim BeginRow As Long, EndRow As Long, ChkCol As Long, i As Long, j As Long, sh As Worksheet
  Dim a() As Variant, r As Range, wFlag As Boolean
  Application.ScreenUpdating = False
  BeginRow = 3
  EndRow = 150
  ChkCol = 36
  For i = 1 To 12
    Set sh = Sheets(MonthName(i, True))
    wFlag = False
    If sh.ProtectContents Then
      wFlag = True
      sh.Unprotect "ainsley"
    End If
    sh.Rows(BeginRow & ":" & EndRow).EntireRow.Hidden = False
    Set r = sh.Range("A" & EndRow + 1)
    a = sh.Range(sh.Cells(1, ChkCol), sh.Cells(EndRow, ChkCol)).Value
    For j = 1 To UBound(a)
        If LCase(a(j, 1)) = LCase("hide") Then Set r = Union(r, sh.Range("A" & j))
    Next
    r.EntireRow.Hidden = True
    sh.Range("A" & EndRow + 1).EntireRow.Hidden = False
    If wFlag Then sh.Protect "ainsley"
  Next
  Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

Since the code for the module listed here is in this thread I thought I'd ask.

Is this error because they are using 2013 version and I used 365 to create? I did the compatibility and it didn't show any errors.

I don't have 365 to try, maybe someone else can answer. For the error message that seems.