Solved

Select worksheet in VB for Excel not working

Posted on 2014-09-03
7
190 Views
Last Modified: 2014-09-03
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
0
Comment
Question by:morinia
7 Comments
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Should be:
Sheets("Product").Select

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Are you certain that MEDICAL_VOID_REBUILD is the name of the sheet?

Also what error are you getting?
0
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 100 total points
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:morinia
Comment Utility
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
 
LVL 45

Accepted Solution

by:
Martin Liss earned 400 total points
Comment Utility
Yes it does and I should have thought of that since you can't Activate or Select a hidden sheet.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now