Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to hide headers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to hide headers
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 "C02C04" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to hide headers
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 "Peter T" wrote: 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 "C02C04" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to hide headers
Excellent! Works perfectly!
Thanks Peter. "Peter T" wrote: 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 "C02C04" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to hide headers
Thanks! I didn't know about this.
"Pflugs" wrote: 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 "Peter T" wrote: 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 "C02C04" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide or Protect Headers | Excel Discussion (Misc queries) | |||
How to Hide Row & Column Headers in Excel ? | Excel Discussion (Misc queries) | |||
Hide Column Headers but not row (Headers) | Excel Programming | |||
Show/Hide Column and Row Headers | Excel Discussion (Misc queries) | |||
Possible to hide column headers??? | Excel Programming |