VBA to hide headers

[Guest]

I have a working file with more than 50 sheets. When I send out to my users I
would like to hide both the row and columns headers in every sheets. Can
someone help?

I am using the below to copy and paste values in every sheet and it worked
very well. Not sure if some coding can be added inside the loop.

Set WB = ActiveWorkbook
For Each SH In WB.Worksheets
With SH.UsedRange
.Value = .Value
End With
Next SH

 

[Peter T]

Try something like the following -

Sub test()
Dim ws As Worksheet
Dim wn As Window

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
For Each wn In ActiveWorkbook.Windows
wn.DisplayHeadings = False
Next
Next

End Sub

Usually it's not necessary to use select or activate, though in the case of
window properties normally need to activate the sheet.. Above caters for
possibility of multiple windows per sheet and the error handler in case a
worksheet chart is active.

Regards,
Peter T

 

[Guest]

I know this is probably a case where you'd prefer to do this through code,
but I would also suggest looking into Bastien Mensink's ASAP-Utilities, a
free add-in that includes a "Vision Control" macro to do just this, even
across multiple sheets. Oh, and there are also 299 other really cool macros.
Check it out at asap-utilities.com

Pflugs

 

[Guest]

Excellent! Works perfectly!
Thanks Peter.

Try something like the following -

Sub test()
Dim ws As Worksheet
Dim wn As Window

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
For Each wn In ActiveWorkbook.Windows
wn.DisplayHeadings = False
Next
Next

End Sub

Usually it's not necessary to use select or activate, though in the case of
window properties normally need to activate the sheet.. Above caters for
possibility of multiple windows per sheet and the error handler in case a
worksheet chart is active.

Regards,
Peter T

 

[Guest]

Thanks! I didn't know about this.

I know this is probably a case where you'd prefer to do this through code,
but I would also suggest looking into Bastien Mensink's ASAP-Utilities, a
free add-in that includes a "Vision Control" macro to do just this, even
across multiple sheets. Oh, and there are also 299 other really cool macros.
Check it out at asap-utilities.com

Pflugs