Select worksheet in VB for Excel not working

Experts,

I am tryhing to selet a worksheet so I can delete rows when the values of all of the columns is 0. I am getting an error on the select statement.  I am passing the string in the call to the routine.  Doesanyone see what the problem is?


****Call
Call HospitalCleanup("MEDICAL_VOID_REBUILD")



***routine

Sub HospitalCleanup(Product As String)
Dim lastrow As Long, i As Long, j As Long 'define variables
Application.ScreenUpdating = False 'disable ScreenUpdating to avoid screen flashes
ActiveSheet.Unprotect
Sheets(Product).Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'get last row

For i = lastrow To 9 Step -1
    If Len(Trim(Cells(i, 1))) > 0 Then
        For j = 1 To 12
            If Cells(i + 2, j * 2 + 1) <> 0 And Len(Cells(i + 2, j * 2 + 1)) > 0 Then GoTo notDelete
        Next
           
        Rows(i).Resize(4).Delete
notDelete:
    End If
Next i
    With Worksheets(Product)
       .Activate
       .Range("B5") = Product
       .Range("G3") = ""
    End With

ActiveSheet.Protect
Sheets(Product).Visible = False
Application.ScreenUpdating = True 'reenable ScreenUpdating
End Sub
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
Should be:
Sheets("Product").Select

Open in new window

0
Martin LissOlder than dirtCommented:
Are you certain that MEDICAL_VOID_REBUILD is the name of the sheet?

Also what error are you getting?
0
MacroShadowCommented:
Oops I was too quick to jump the gun...
The code looks ok (workable...) if the sheet MEDICAL_VOID_REBUILD exists I don't see a problem.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

moriniaAdvanced Analytics AnalystAuthor Commented:
Experts,

The sheet is "MEDICAL_VOID_REBUILD ".  I think the problem may have been the sheet was hidden. Does that sound correct. I made the sheet visible and I did not see the error.
0
Martin LissOlder than dirtCommented:
Yes it does and I should have thought of that since you can't Activate or Select a hidden sheet.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Thanks for the points but there's no need to assign them when you self-resolve a problem. If you 'Request Attention' I won't have any objection if you ask the moderator to help you remove the point assignment.
0
NorieVBA ExpertCommented:
I know this is solved but you should be able to do what you want without selecting/activating, or making the sheet visible.
Sub HospitalCleanup(Product As String)
Dim lastrow As Long, i As Long, j As Long    'define variables

    Application.ScreenUpdating = False    'disable ScreenUpdating to avoid screen flashes

    With Sheets(Product)
        .Unprotect
        lastrow = .Cells(Rows.Count, "A").End(xlUp).Row    'get last row

        For i = lastrow To 9 Step -1
            If Len(Trim(.Cells(i, 1))) > 0 Then
                For j = 1 To 12
                    If .Cells(i + 2, j * 2 + 1) <> 0 And Len(.Cells(i + 2, j * 2 + 1)) > 0 Then GoTo notDelete
                Next j

                .Rows(i).Resize(4).Delete
notDelete:
            End If
        Next i

        .Range("B5") = Product
        .Range("G3") = ""

        .Protect
    End With
    
    Sheets(Product).Visible = False
    Application.ScreenUpdating = True    'reenable ScreenUpdating
End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.