Using macro with hidden worksheet

[Jack77]

Hi,

If I hide a worksheet how I can use it with a macro?

This works when worksheet is visible:

Sheets("worksheet").Select

If I hide the worksheet and try to run the same macro I
get an error.

Any help would be appreciated!

 

[Frank Kabel]

Hi
you could try something like the following:
sub foo()
dim wks as worksheet
set wks = worksheets("worksheet")
msgbox wks.range("A1").value
end sub

 

[Otto Moehrbach]

Jack
As you saw, you cannot select a hidden sheet. If you want to select the
sheet, first unhide it with:
Sheets("Worksheet").Visible = True
But you don't need to select or unhide a sheet to place data in it or to
read/copy data from it. You do need to unhide it if you want to print it.
What do you want to do with the sheet? HTH Otto

 

[Tom Ogilvy]

You don't need to select a sheet to work with it. I assume you have
recorded a macro and gotten code like this. The reason is that the recorder
records your actions and your actions included selecting the sheet. You can
generally change code like

worksheets("Sheet1").Select
Range("a1").Select
Selection.Value = 3

to

worksheets("Sheet1").Range("A1").Value = 3

which does not require Sheet1 to be active or visible since you provide a
reference to where you want the action to occur.

for a series of actions

With Worksheets("Sheet1").Range("A1")
.Value = 3
.Interior.ColorIndex = 5
With .Font
.Name = "Arial"
.Size = 3
.ColorIndex = xlAutomatic
End With
End With

As an example.

 

[Bob Phillips]

Hi Jack,

As Select will want to put the sheet in the window, it needs to be visible.
You could select it beforehand, and suppress screenupdating so it isn't
seen, and hide it after

application.screenupdating = false
Sheets("worksheet").visible = true
Sheets("worksheet").Select
' do your stuff
Sheets("worksheet").visible = true
application.screenupdating = true

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Paul B]

Jack, do you have to select the sheet to do what you want? You can do most
things without selecting the sheet, like below

Instead of
Sheets("worksheet").Select
[A1] = "Test"

Use this, it will work on a hidden sheet

Sheets("worksheet").[A1] = "Test"

Or if you have to select the sheet you could unhide the sheet run your code
and then hide the sheet, something like this

Application.ScreenUpdating = False
Sheets("Worksheet").Visible = True
Sheets("worksheet").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Test"
Sheets("Worksheet").Visible = False
Application.ScreenUpdating = True


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **