Working with a worksheet that is not the active worksheet

How can I make this code work on my worksheet named February, when it is not 
the active worksheet?


Sub BlankWeeks()
'
' Macro2 Macro
'

'
    If Range("C184").Value = "" Then
        Rows("184:228").Hidden = True
    End If

    If Range("C184").Value <> "" Then
        Rows("184:228").Hidden = False
    End If

    If Range("C229").Value = "" Then
        Rows("229:273").Hidden = True
    End If

    If Range("C229").Value <> "" Then
        Rows("229:273").Hidden = False
    End If

End Sub 

0
ordnance1
4/14/2010 8:26:03 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
719 Views

Similar Articles

[PageSpeed] 48

Hi,

You have to qualify the ranges with a sheet name

Sub BlankWeeks()
Set sht = Sheets("February")
With sht
    If .Range("C184").Value = "" Then
        .Rows("184:228").Hidden = True
    End If

    If .Range("C229").Value <> "" Then
        .Rows("229:273").Hidden = False
    End If
End With
End Sub
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"ordnance1" wrote:

> How can I make this code work on my worksheet named February, when it is not 
> the active worksheet?
> 
> 
> Sub BlankWeeks()
> '
> ' Macro2 Macro
> '
> 
> '
>     If Range("C184").Value = "" Then
>         Rows("184:228").Hidden = True
>     End If
> 
>     If Range("C184").Value <> "" Then
>         Rows("184:228").Hidden = False
>     End If
> 
>     If Range("C229").Value = "" Then
>         Rows("229:273").Hidden = True
>     End If
> 
>     If Range("C229").Value <> "" Then
>         Rows("229:273").Hidden = False
>     End If
> 
> End Sub 
> 
0
Utf
4/14/2010 8:50:08 AM
You have to tell the VB processor the name of your worksheet which you can 
do using the Worksheets collection. There are two ways to do this. First, by 
directly qualifying each range reference...

Sub BlankWeeks()
    If Worksheets("February").Range("C184").Value = "" Then
        Worksheets("February").Rows("184:228").Hidden = True
    End If
    If Worksheets("February").Range("C184").Value <> "" Then
       Worksheets("February").Rows("184:228").Hidden = False
    End If
    If Worksheets("February").Range("C229").Value = "" Then
        Worksheets("February").Rows("229:273").Hidden = True
    End If
    If Worksheets("February").Range("C229").Value <> "" Then
        Worksheets("February").Rows("229:273").Hidden = False
    End If
End Sub

or second (the much cleaner looking way), by using a With/End With block...

Sub BlankWeeks()
    With Worksheets("February")
        If .Range("C184").Value = "" Then
            .Rows("184:228").Hidden = True
        End If
        If .Range("C184").Value <> "" Then
            .Rows("184:228").Hidden = False
        End If
        If .Range("C229").Value = "" Then
            .Rows("229:273").Hidden = True
        End If
        If .Range("C229").Value <> "" Then
            .Rows("229:273").Hidden = False
        End If
    End With
End Sub

Notice the "dots" in front of each range reference (Range, rows, etc.)... 
those are required in order that the range references back to the object of 
the With statement.

-- 
Rick (MVP - Excel)



"ordnance1" <ordnance1@comcast.net> wrote in message 
news:F65C8F5C-9CF8-415A-8BC7-D3C6A1A9CDE8@microsoft.com...
> How can I make this code work on my worksheet named February, when it is 
> not the active worksheet?
>
>
> Sub BlankWeeks()
> '
> ' Macro2 Macro
> '
>
> '
>    If Range("C184").Value = "" Then
>        Rows("184:228").Hidden = True
>    End If
>
>    If Range("C184").Value <> "" Then
>        Rows("184:228").Hidden = False
>    End If
>
>    If Range("C229").Value = "" Then
>        Rows("229:273").Hidden = True
>    End If
>
>    If Range("C229").Value <> "" Then
>        Rows("229:273").Hidden = False
>    End If
>
> End Sub 

0
Rick
4/14/2010 2:17:23 PM
Reply:

Similar Artilces:

Service Activities
We would like Appointments/Service Activities to show up in Outlook Web Access for our tech's so they can check for new appointments/service activities throughout the day. The problem as I understand it is, if they have their laptops with them, there is no way for Outlook to get updates from CRM (short of VPN) and so OWA wouldn't be current. Does anyone know of a way whereby our Dispatcher could schedule a service call or appointment and it would show up in OWA even though their laptops/CRM Outlook clients were disconnected from the network? Thanks, Neil ...

"Microsoft Outlook Stopped Working" error
Hi all, may I firstly apologise if I seem stupid in anything I say here; I have always used another PIM programme in the past with only the occasional dalliance with any version of Outlook. I am running Vista (Fully updated) Home Premium on a Tosh laptop (32 bit) 1.73 Gb 2GM Ram. I recently installed Outlook 2007 (I also have Office Home & Student 2007 (service desk edition?) which I have been using for a year or so.) The problem is that I keep getting this error when using Outlook. (for example 4 time in 15 mins). I have removed the programme (to ensure no trace o...

Move worksheet to new book
I have 14 worksheets within a workbook. In the middle are worksheets named Sheet1, Sheet2....Sheet10 Is it possible using a macro to see if there is anything written in cell A1 on Sheet1, and if there is, then move it to a new book, and then check Sheet2 and so on? And if there is no information on the worksheet I would like to delete it. TIA Try this against a copy (since it destroys worksheets): Option Explicit Sub testme01() Dim wks As Worksheet Dim iCtr As Long Dim wkbk As Workbook Set wkbk = ActiveWorkbook For iCtr = 1 To 10 'sheet1 throu...

Microsoft Active Directory
I am getting the following error when trying to modify anything on the Exchange General Tab of a user object: The operation failed. ID no: 80004005 Microsoft Active Directory - Exchange Extension I believe this is a permissions issue. Any ideas? Does this happen to all user objects ? What permissions do you have ? Are any of the fields on the user object not completed i.e. initials, first name, display name etc as per http://support.microsoft.com/kb/314034/en-us Regards Paul Ford Edge IT Ltd "COT-AD" <COTAD@discussions.microsoft.com> wrote in message news:C50D72ED-...

OWA not work properly
When users go to http://sitename/exchange they get the yellow OWA screen without the graphics. If I enter username, I get "HTTP Error 404 - Not Found". I have NT 4.0 SP6a, Exchange 5.5 SP4, and IIS 4.0 with all the latest updates. In Internet Service Manager, in the Default Web Site properties, I have the redirect to the above URL in the Home Directory tab. Under the Directory Security tab I have both Anonymous and Challenge/Response. In the Exchange folder properties (in Internet Service Manager) under the Virtual Directory tab I have the Local Path "c:\exchsrvr\webdata&quo...

Worksheet Filtering by UserName
I have an excel workbook with 60 tabs (worksheets) that contain user production data and I need a way that when someone opens up the workbook they only see the (worksheet) tab with their information only and not the entire workbook. Is there a way to do this in excel? Thank you! Probably the best way to do this is to create a sheet and name it something like 'Passwords' and put a command button in that sheet and point it to this code: Private Sub CommandButton1_Click() Dim i_pwd As String i_pwd = InputBox("Please Enter Password to Unhide Sheet", &qu...

Mouse scroll not working in Module window
Access97 on HP Laptop on XP Pro Since installing Office 97 on my new (replacement) laptop I find the mouse scroll button does not work in the Module window - works everywhere else in Access ok? Any ideas appreciated. Piri yeah don't use ancient software, kid "Piri" <wiremu.pareiha@hotmail.com> wrote in message news:1178247184.740213.222110@y5g2000hsa.googlegroups.com... > Access97 on HP Laptop on XP Pro > Since installing Office 97 on my new (replacement) laptop I find the > mouse scroll button does not work in the Module window - works > everywhere else i...

Auto-create activity from Mobile
Hi all, We have CRM 4.0 Roll Up 7. We are looking for a solution that offers advanced mobile integration to CRM. We would like the mobile client to auto-generate an Activity in CRM whenever a phone call is made to a CRM contact. Has anyone done this? //Johan Johan; There are a few vendors that offer good mobile solutions. I know for certain that MobileAccess from Ten Digits (http://www.tendigits.com/) has that specific feature. Dave Ireland "Jobro" <Jobro@discussions.microsoft.com> wrote in message news:4F1B3A1D-E23E-4E49-933F-B221C0DD1ECE@microsoft.com... >...

HQ Options not working
Hi! When I open the HQ Configuration window, there is a list of Checkboxes for "Enabled" - "Disabled" for : - Automatically create inter-store receive inventory order - Automatically create inter-store issue inventory order and other! This window doesn't work, and when accepting changes, and open the window again, all checkboxes are clear!! And of course, those options do not work neither! I'm working by lauching inter-store trasnfers from HQ, but would like to have stores deciding this by its own. Anybody know something about this. Maybe how to set this featu...

Exchange 2010 & Active Sync
Hi All, Running Exchange 2010. I have my phone (iphone 3gs) syncing with exchange 2010 perfectly. However, trying to sync a new plam pro (win mobile 6.1) and getting errors. I think it's the account as doing a test-ActiveSync-Connectivity gives the following: Any ideas? RunspaceId : 962cea80-32ae-4e60-a5f1-67ed96cab519 LocalSite : Default-First-Site-Name SecureAccess : True VirtualDirectoryName : Url : UrlType : Unknown Port : 0 ConnectionType...

Selecting Pics in Explorer, then "Send via e-mail" doesn't work...
I'm sure this has been addressed, but I haven't found it in a search. I'm running Outlook 2007 and it worked fine with XP - since I've upgraded to Windows 7, I can't select *any* files in explorer and send them via e-mail. Nothing happens, then I can't get Outlook to start under any circumstance unless I reboot. I need to create a new e-mail, then select the files...and it works fine, but it's a giant PITA. Any help: Sounds like it is caused by a corruption or an incompatible add-in. Which add-ins do you have installed? Tools-> Trust Center-&g...

Button form control does not work
I have spent over 5 hours trying to get the button wizard to do a simple thing. Open a form and show data in that form that matches the name in the form where the button is but when i follow the wizard the button allways go to the first entary in the second form eg i have the form (form1) open on emma (record 3) and want to open a second form that also contains emma the wizard puts the following in to a macro form2, Form, , "[Full Name]=" & [Full Name], , Normal but when i test the button it open the second form with the first record which is not emma why d...

How to CreateTournament Worksheet
I'm not an experienced user of excel but want to create a worksheet sheet to work in a tournament format with picture, have two picture next to each other click on one and it moves it to the next round. I have around 20 pictures of teams and which need to gradually reduce to a winner in the final. Can anyone help? Is there a template to get hold of. Thought someone will have already done some thing like this for football teams? -- Jarvis ------------------------------------------------------------------------ Jarvis's Profile: http://www.excelforum.com/member.php?action=getinfo&...

will RMS 2.0 work with Windows 7
Has anyone tried using RMS 2.0 with Windows 7? What version RMS are you using? Yes 2.0.0126 -- Maurice Gordon Maurice@americanretailsupply.com Sales/Support 1-800-426-5708 XT 1307 "Larry" <Larry@discussions.microsoft.com> wrote in message news:0E0D2035-1FB0-497E-8F73-AF5FDA947E64@microsoft.com... > Has anyone tried using RMS 2.0 with Windows 7? What version RMS are you > using? Hi Larry, RMS 2.0 SP2 working fine with windows 7 Vijay "Larry" wrote: > Has anyone tried using RMS 2.0 with Windows 7? What version RMS ar...

Excel Question: How to Delete All Blank Rows from a worksheet?
Hi, I'm brand new, and that is my question. I just spent 2 hour laboriously mousing along deleting row by row, every other row thinkin there's gotta be a better way. (I could've done it of course if the were adjacent, but they were interspersed with my good data). I'm little behind the times: agile with keystrokes but less so with mous [think I was quicker in Lotus DOS! (actually "TWIN")]. But I do play mean piano -- Message posted from http://www.ExcelForum.com Check out: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows "DaveThePianoGuy >...

How do I sort a proctected worksheet
Unprotect it. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Audrey" <Audrey@discussions.microsoft.com> wrote in message news:1E1AC0AE-67F0-46B1-A741-B32D4DF69D43@microsoft.com... > That is a given, but I have columns that I don't want sorted. I have locked those columns in hopes they would not sort and the others would. Any suggestions? Thanks for the help. "Chip Pearson" wrote: > Unprotect it. > > > -- > Cordially, > Chip Pearson > Microsoft MVP - Excel > Pearson...

How to control what tabs are displayed in Active Directory Users and Computers
I do not see the value to allow the delegate to change the City, State, Country, Office. What am I doing wrong. (see info below) Also is there a way to limit the view of what is shown in Active Directory users and computers, so the Delegate only sees the General, Address, Telephone and Organization tabs? I know the step below lock them out of the other tabs, but there would be less confusion..even if it is registry hack on that local pc. Here are the steps I have done so far: Step 1. Create a Security Group Called 'XYZ' (call it what ever you want Step 2. Open Active Directory User...

saved passwords not working
I have 4 email accounts in outlook 2002. Everytime Outlook is started, it prompts me for my passwords on each account, even though they are saved. It only does this when it is started for the first time and it saves the passwords for the rest of the session. But, the next time Outlook is restarted, it prompts me for the passwords again. I have SP2 installed and have searched the support KB for this problem. Anyhelp would be appreciated. Carley@CarleyCapers.com ...

Attaching a worksheet to an e-mail
Hi! I am working with excel 2003 I would like to attach a worksheet (not the entire workbook) to a e-mail. I would like to do it as an excel worksheet if possible but can make it work as a body of text as well. What would the code loo like for this. The user is accessing my workbook file (read only) through a websit and when they fill-out the form they click a button and the macr e-mails the form worksheet to my e-mail address. This is an attempt a doing "On-Line" warranty claims. Any help or suggestions would be greatly appriciated -- Brian Matlac --------------------------------...

Scrolling not working in spreadsheet (Excel 2000)
Good afternoon. We have Excel 2000 running on Windows 2000. I have a spreadsheet with a column that is filtered by Status - Open or Closed. If I am showing the 'Open' items, I can page up and down, use the up/down arrows and the mouse to scroll up and down in the document. If I show the 'Closed' items, none of the scrolling options work. The scroll bar on the right indicates that I'm 'moving' down in the document, and the cell reference in the upper left corner changes (as does the display of the cell contents) but all I see on the screen is the first page. An...

Worksheet Help req PLZ
Hey All, I have a small issue where I have created a worksheet called data and in Cell A3 I have a linked cell to another worksheet and cell "WK48!A2" In Cell A4 in the "data" worksheet I need a link to "WK49!A2". I would like to link all cells in Column A to consecutively "WKxx" worksheet once I add them to the workbook. I thought I could drag them mouse down but al I get is links to consecutive cells in "WK48" work sheet. Please help mag()() Try =INDIRECT("'WK"&ROW(48:48)&"'!A2") -- Reg...

TimerInterval not working
I have an access db application which needs to call the macro on startup. The macro is to call the function opening two forms on startup and set the TimerInterval of two forms. Public Function auto_login() DoCmd.OpenForm "frm_A" Forms![frm_Import&Export].TimerInterval = 300000 DoCmd.OpenForm "frm_B" Forms![frm_TREATS_ftp].TimerInterval = 120000 End Function However only the Timer for form B (at the toppest) works. It works for form A but not B if I open form B first and then A later. Is there any way to solve the problem? Regards Caris Caris, This seems a litl...

Re: Suggest Name Now Not Working
"Karl Burrows" <kfb1@spambellsouth.net> wrote in message news:... > My bad, it's the automatic name checking I use, not the suggest name. I get > confused between the two. Name checking is the one you get the red or green > underline squiggle to right-click and complete the email address. > > It's been a long week! > > PS Just got your Outlook Programming book. > > "Sue Mosher [MVP-Outlook]" <suemvp@outlookcode.com> wrote in message > news:OIBsc8XgEHA.3864@TK2MSFTNGP10.phx.gbl... > > Did you check your setting in T...

On-line quotes server does not work
OK - there are numerous posts in this Newsgroup stating that the on-line update to stock quotes and news does not work - everyone getting the "unable to connect to quotes server" error. Is it working for anyone? I switched from Quicken to Money and I'm about to switch back - at least Quicken can do something as simple as downloading quotes from a server reliably. Anyone home at Microsoft? On Tue, 3 Feb 2004 21:40:12 -0800, "Anyone home at Microsoft? Hello?" <fbella@aol.com> wrote: > >OK - there are numerous posts in this Newsgroup stating >that...

does any one know how to convert a scanned sheet to a work sheet
does any one know how to convert a scanned sheet to a workable sheet in excel You could use OCR (optical character recognition) software to convert the image to text that could then be loaded into cells. My experience with OCR software has not been entirely satisfactory. Unless this is a large sheet, it may be easier to retype. Jerry bigjoe44 wrote: > does any one know how to convert a scanned sheet to a workable sheet in excel ...