HELP! Why doesn't this work?

  • Thread starter Thread starter Randy Numbers
  • Start date Start date
R

Randy Numbers

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
 
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
 
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
 
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?
 
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
 
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
 
Back
Top