HELP! Why doesn't this work?

I'm trying to affect several worksheets from a single macro. The WITH
Worksheet("Xyz") command, and following code, seems to work OK when the
macro is invoked when sheet "Xyz" is active, but doesn't hit XYZ if called
from another sheet. I thought the WITH would make the Active sheet
irrelevant.  What don't I get?

Sub testing()
With Worksheets("xyz")
..Range("c4") = "background" <=== this works fine
Cells.Select
Selection.EntireRow.Hidden = False <===this seems to be affecting only
active sheet
Selection.EntireColumn.Hidden = False
End With
End Sub



Thanks../Randy

0
ran (54)
9/2/2004 6:04:35 PM
excel 39879 articles. 2 followers. Follow

5 Replies
763 Views

Similar Articles

[PageSpeed] 54

Not if you use selection in your code

Try this

Sub testing()
    With Worksheets("Xyz")
        .Range("c4") = "background"
        .Cells.EntireRow.Hidden = False
        .Cells.EntireColumn.Hidden = False
    End With
End Sub


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Randy Numbers" <ran@hotmail.com> wrote in message news:2fJZc.74780$ve7.29481@fe42.usenetserver.com...
> I'm trying to affect several worksheets from a single macro. The WITH
> Worksheet("Xyz") command, and following code, seems to work OK when the
> macro is invoked when sheet "Xyz" is active, but doesn't hit XYZ if called
> from another sheet. I thought the WITH would make the Active sheet
> irrelevant.  What don't I get?
>
> Sub testing()
> With Worksheets("xyz")
> .Range("c4") = "background" <=== this works fine
> Cells.Select
> Selection.EntireRow.Hidden = False <===this seems to be affecting only
> active sheet
> Selection.EntireColumn.Hidden = False
> End With
> End Sub
>
>
>
> Thanks../Randy
> 


0
rondebruin (3790)
9/2/2004 6:16:36 PM
Ron,

Thanks... that worked.

I also want to be able to hide certain columns when done.

I've tried
 Range("a:a").EntireColumn.Hidden = True

and
Columns("A:A").Select
    Cells.EntireColumn.Hidden = True

neither works.   ./R

0
ran (54)
9/2/2004 6:33:01 PM
Ron,

I figured the last out with:
..Columns("a:a").Cells.EntireColumn.Hidden = True

Now I want to end by making cell D9 active

 .Range("d9").Select

doesn't work. I tried some variation with
 .cells.range("D9").select...


You mentioned SELECT won't work with WITH -- what to use instead?

0
ran (54)
9/2/2004 6:44:31 PM
Then use this (don't use select)

Sub testing()
    With Worksheets("Sheet1")
        .Range("c4") = "background"
        .Cells.EntireRow.Hidden = False
        .Cells.EntireColumn.Hidden = False
        .Range("A1").EntireColumn.Hidden = True
    End With
End Sub


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Randy Numbers" <ran@hotmail.com> wrote in message news:IFJZc.67459$RU5.38849@fe31.usenetserver.com...
> Ron,
>
> Thanks... that worked.
>
> I also want to be able to hide certain columns when done.
>
> I've tried
> Range("a:a").EntireColumn.Hidden = True
>
> and
> Columns("A:A").Select
>    Cells.EntireColumn.Hidden = True
>
> neither works.   ./R
> 


0
rondebruin (3790)
9/2/2004 6:49:00 PM
Hi Randy

You can't select the cell if the sheet is not the active one.
You can use this to select the sheet and the cell

Sub testing()
    With Worksheets("Xyz")
        .Range("c4") = "background"
        .Cells.EntireRow.Hidden = False
        .Cells.EntireColumn.Hidden = False
        .Range("A1").EntireColumn.Hidden = True
        Application.Goto .Range("D9"), True
    End With
End Sub


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Randy Numbers" <ran@hotmail.com> wrote in message news:vQJZc.27566$Vx4.20699@fe08.usenetserver.com...
> Ron,
>
> I figured the last out with:
> .Columns("a:a").Cells.EntireColumn.Hidden = True
>
> Now I want to end by making cell D9 active
>
> .Range("d9").Select
>
> doesn't work. I tried some variation with
> .cells.range("D9").select...
>
>
> You mentioned SELECT won't work with WITH -- what to use instead?
> 


0
rondebruin (3790)
9/2/2004 6:52:30 PM
Reply:

Similar Artilces:

Rule won't work
Outlook 2007 I have set up a rule to do the following; When I send or receive a message from a specific contact, play a sound, display in new item alert window, flag for follow up and move to a specific folder. But it is not doing anything. Do I need to change something else? What type of email account? Do other rules work? Does it work if you use any one action? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.out...

Retore HELP
Does anyone know exactly how to restore the information store or even a mailbox from the Windows Backup. The backup that I did was successful and backed up everything in the information store, but I confused on how to restore the mailboxes. The restore ask for a path, but I am not sure what to type in for the path ThankYou MSKB 258243 describes the details in a backup and restore of the Exchange store using Windows backup: http://support.microsoft.com/default.aspx?scid=kb;EN-US;258243 "Thomas White" wrote: > Does anyone know exactly how to restore the information store or...

Search Sub Form Help :)
Hi there, I know this question has probably been asked a million times, but for a n00b it's kinda hard to find the time at work to search similar threads, so apologies in advance! :) I take my issue is quite basic, so here's the jist: I currently have: 1 Table (data), 1 Add Form (fields are bound to the table), 1 Search Form (4 unbound fields) where by populating the fields with similar data captured in the existing table and clicking the "Search" cmd button, a query is activated and a new table pops up with the search results. I require: a SubForm in th...

Please Help With Small VBA Problem...
I know I am overlooking something small, but I just cant seem to get a variable (or for that matter the result of a function) from one sub to another. Thats probably not very clear so I am including the relevant portions of code.... Sub WhatToDoWithIt(qCell As Range, qColor As Integer) 'On Error Resume Next Dim AnsWhat As String, ColHeader As String, NumShts As Integer, HeaderRow As Integer, HdrNumOfColumns As Integer HeaderRow = GetHdrRow(ActiveSheet) <<<<<<<<<<<<<<<<<<<<Problem is here, can not get results from function on ...

Please help!!Can not send/receive emails
I am running exchange 2003...and I can not send or receive any emails. It was working at 10am and now at 11:52am..it no longer works. All the service are up and running and there isn't any messages queued in my system...but still I can not receive anything...I rebooted my server and still no new emails..can someone please help me!! There could be many reasons for this but first make sure the Default SMTP server (Not the service - though that needs to be running as well) is running and that you have connectivity. Also try to telnet to the server. And, as always, check the event log...

Need help solving a threading issue
Hi folks, I have an MFC application that has some GUI functionality. It's main thead creates a worker thread (using AfxBeginThread), and this worker thread needs to create an instance of a class (which we'll call 'network' for now) which does some TCP/IP networking stuff; functionality achieved by inheriting from another class. Also, The 'network' class and the main thead talk to each other via PostMessage. Now my problem is that I can't figure out how to instantiate the network class to get everything to work. Here's my attempts and resultant failures: AT...

Variable data range help
I'm looking to build a variable data range to help in some charting. I've tried a few forumla/text combos and have had no luck thus far. I'm wondering what is exceptable to use in the data range feild. Is the string I've tried. Basically I'm looking to create an open ended data range. Any help?? ='Chart Info'!$H$40:$I$"&COUNT(I:I)+39&",'Chart Info'!$M$40:$N$"&COUNT(I:I)+39 Thanks! Ryan -- low98 ------------------------------------------------------------------------ low98's Profile: http://www.excelforum.com/membe...

disable TEMPLATE HELP
"Template help" keeps popping up every time I open a file. Is there a way to disable this annoying function? maybe... Help|Microsoft Help|Online Content Settings|Online Content Category Turn off what you don't want. Simplicity wrote: > > "Template help" keeps popping up every time I open a file. Is there a way to > disable this annoying function? -- Dave Peterson ...

can any one help me in viewing DHCP active leases database ? #2
hi, i am working on a project, as part of the module i have to retrive the ipaddress which r dynamically alloted by DHCP server to all it's DHCP client, these dynamically alloted ipaddress r maintained in active leases database of DHCP server how i am able to view or retrive the data from DHCP server using MFC or is there any other way in getting dynamically alloted ipaddress from DHCP server, i have struck up with this. pls can any one help me in this . my advance thank's to any body answer me and get me out of this prob. byeee lucky u can also ...

Help please ! Creating new account problem
Good Day... When in active dir I go to create a new user exchange doesn't create the mailbox. I go into the user profile in AD but there is no x400 or smtp address. I'm not sure why this stopped working. Any help would be appreciated Are you running Active Directory Users & Computers from a machine where Exhange System Manager has been installed? If ESM has been installed on the machine (and you've tried ADUC on another machine that as ESM), then you might want to try troubleshooting using the following article: 297801 Troubleshooting Check Name Errors http://suppo...

LoadString() not working
I have a resource only DLL that contains all string related resources. I use LoadString() to retrieve my string resources which has worked until now. I added two additional string resources and these particular resources fail to load. All original string resources load fine, it's just these two new ones that don't. GetLastError() returns 0. I've tried Rebuild All, I've tried deleting generated files like .aps, .ncb, .plg, ..opt. What else could it be? Thanks, Drew There is a call to set the resource handle to direct the resources to be loaded from your DLL. Look at AF...

Outlook 2002 Custom Auto Formatting not working!
I created cutom rules to color my tasks differently according to the first two letters of the category name. The coloring worked correctly in Outlook 2000 .. but since upgrading to 2002 the formatting does not work! It seems to only be when formatting according to category .. any other field is happy. Here's an example rule: All personal related categories start with the suffix 'p- ' .. so for financial I have 'p-financial'. To color all personal categories blue in the task list I use the rule: [categories][contains][p-] Any ideas? Is there a patch for this? Thanks...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...

formulas not working
My formulas are correct and reference the appropriate cells, but when I enter data to the referenced cells the formulas do not calculate a value. This is occuring on several of my spreedsheets. If hitting F9 makes it update, then try the following:- Check Tools / Options / Calculation Tab / Set to Automatic and not Manual -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- Attitude - A little thing that makes a BIG difference -...

Excel Save / Saveas / Tools- Customize buttons not working
When I click on Save or Saveas button in Excel there is no response from it. My OS is Win ME. How do I solve it. I have re-installed Office but the problem still remains. Hi Brian! With a re-installation of Office it looks like one of two things: A macro in your Personal.xls file Or A case of the NIMDA virus. Try out virus removal first as it is quicker than another re-install. Here's the site for downloads of various virus removal tools: http://securityresponse.symantec.com/avcenter/tools.list.html You'll see Nimbda in the right hand side list twice. You should download both...

URLs in HTML email often don't work
Anybody notice how if you put URLs in HTML emails, they don't work? The HTML code adds a lot of alphanumeric stuff in front of the URL so it can't be located. I don't recall this problem prior to Vista Mail. So, if I want to be sure they'll work I'll have to turn off the HTML. Or, I'll save it as a draft then see if the URLS work- if they work in the draft folder, they'll usually work OK. comments? Joe Copy & pasting a link in a new message window will not be clickable. It will be in Drafts and Sent Items once sent. It was like this with ...

Can't get trigger to work with linked SQL Instance...
I'm trying to create a trigger on the Customer Master Table (RM00101) that will insert certain fields from a new record into another table on a seprate instance of SQL. I have linked the 2nd instance to the first from within Enterprise Manager. Whenever a new Customer record is created and saved the following SQL exception is thrown : "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query" I have checked several sources for the cause...

how can i work with the templates wizard in excell 2003?
how can i work with the templates wizard in excell 2003? In old versions i worked with templates wizard to create data in access or in a worksheet. i setuped the excell 2003 pro and then i canot work with previously worksheets created with the old wizard. ...

Help with formuala
I have a database with a number of address listings. I know that there are duplicate accounts in my list. Right now the only way that I know how to do it is to sort by address and then look for duplicates. Is there a formula that I can use. I am familiar with Vlookup, etc, but can't find one that works. Thanks, A.Pon Check out http://cpearson.com/excel/duplicat.htm In article <IxZwd.215391$V41.7862@attbi_s52>, "AAP" <mdsl_fusion@comcast.net> wrote: > I have a database with a number of address listings. I know that there are > duplicate accounts in my...

Please Help with Multiple Field Primary Keys
I have two tables in a database that have to use four fields for a unique identifier and primary key. How do I set up a query to set a relationship between those four fields together as one? When I add the tables to the query I see the fields, but I am not having any success properly setting the relationships. The upper half of the query design window is where the 2 tables appear. Drag Field1 from Table1, and drop it into Field1 from Table2. Access displays a join line from one table to the other. Drag Field2 from Table1, and drop it into Field2 from Table2. Repeat for the other 3 f...

word stopped working
I saved file on a flash drive. When I tried to open it the next day, I got the message Word has stopped working. MY OS is Vista. Thanks for any help. CHV NEVER save a document to a flash drive. It is almost certain that doing so will cause it to become corrupted. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "cvandy" <cvandy@discussions.microsoft.com> wrote in message news:0F60DF14-8EF6-4407-8DFC-91B1655...

How can I use this expression "=SUM(RC[a0]:RC[a1])" work in Excel
I need to insert this formula "=SUM(RC[s0$]:RC[s1$])" in a cell such that the values a0 and a1 are variables. How can this be done? Please assist and advice. dim s0 as string 'I'd use long's dim s1 as string s0 = "3" s1 = "6" somecell.formular1c1 = "=SUM(RC[" & s0 & "]:RC[" & s1 & "])" funmi_Bash wrote: > > I need to insert this formula "=SUM(RC[s0$]:RC[s1$])" in a cell such that the > values a0 and a1 are variables. > How can this be done? Please assist and advice...

Help with Formulas
I need help with creating a formula. I want to count the total number of individuals who meet a certain criteria in one column and a certain criteria in another column. For example: I want to find out the total number of people who live in Germany and went on a specific trip. For argument sake let's say column A is at lists only Y or N for yes and no, and column B is a list of Countries such as Germany, America, etc. I would like to figure out the total number of individuals who have a Y in column A and the word Germany in column B. This is what I have done so far and it works for co...

need help
Humaneyes from Israel really sucks! ...

Excel Help
When I have a workbook open and then open Microsoft Excel Help, it puts the Help screen on the right hand side. Trying to get the Help to full-screen and the Excel sheet to full-screen is then an incredibly difficult task. Is there a way to get Excel Help to open in full-screen and not affect the size of the screen with the spreadsheet, which is full-screen also? I didn't see anything in the Help section. Thanks for any info. Hi, This worked for me: open Help and adjust the sizing you like. Close it. close your spreadsheet; reopen and see if help doesn't come up the way you left ...