Good Morning,
I borrowed this code and tried to modify to do what I need with no luck. I have 7 sheets in a workbook named MON-SUN. Mon is the sheet with blanks for data entry. Tues-Sun have formulas to transfer the info through the selected sheets. I would like to have a macro on sheet Mon that hides all the blank rows and I would like that to hide the same rows on Tues-Sun. This is what I have so far
Dim wsMySheet As Worksheet
Dim lngMyRow As Long
Application.ScreenUpdating = False
For Each wsMySheet In ThisWorkbook.Sheets
Select Case wsMySheet.Name
Case Is = "MON", "TUES", "WED", "THUR", "FRI", "SAT", "SUN"
For lngMyRow = 73 To 10 Step -1 'Need to work backwards through the rows when hiding or deleting
If Len(wsMySheet.Range("A" & lngMyRow)) = 0 Then
wsMySheet.Range("A" & lngMyRow).EntireRow.Select
Sheets(Array("MON", "TUES", "WED", "THUR", "FRI", "SAT", "SUN")).Select
wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = True
Else
wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = False
End If
Next lngMyRow
End Select
Next wsMySheet
Application.ScreenUpdating = True
End Sub
It works great on Mon but then stops. Line 9 is the error line.
Any help will be appreciated!
I borrowed this code and tried to modify to do what I need with no luck. I have 7 sheets in a workbook named MON-SUN. Mon is the sheet with blanks for data entry. Tues-Sun have formulas to transfer the info through the selected sheets. I would like to have a macro on sheet Mon that hides all the blank rows and I would like that to hide the same rows on Tues-Sun. This is what I have so far
Dim wsMySheet As Worksheet
Dim lngMyRow As Long
Application.ScreenUpdating = False
For Each wsMySheet In ThisWorkbook.Sheets
Select Case wsMySheet.Name
Case Is = "MON", "TUES", "WED", "THUR", "FRI", "SAT", "SUN"
For lngMyRow = 73 To 10 Step -1 'Need to work backwards through the rows when hiding or deleting
If Len(wsMySheet.Range("A" & lngMyRow)) = 0 Then
wsMySheet.Range("A" & lngMyRow).EntireRow.Select
Sheets(Array("MON", "TUES", "WED", "THUR", "FRI", "SAT", "SUN")).Select
wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = True
Else
wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = False
End If
Next lngMyRow
End Select
Next wsMySheet
Application.ScreenUpdating = True
End Sub
It works great on Mon but then stops. Line 9 is the error line.
Any help will be appreciated!