Issue with marco for entire workbook

[Mark G.]

I am trying to set a simple macro that will hide 1 certain column ('J', in
this case) on each worksheet with my workbook. At the same time, I would
like it to close a particular worksheet (the last one in the string of them
all - about 14 worksheets within workbook). Need these set to 'save to web'
for this database I have going. I have tried this a few methods to include
keeping it relative, but no luck. I can do them individually for each
worksheet, but can not for the lump some of them with one simple macro. Got
to be something simple I am missing. Checked through my books with no luck.
So if you could, would be nice to fill me in with what I am doing wrong and
how do this effectively. Thanks much.

 

[Nick Hodge]

Mark

I'm struggling to understand what you mean by 'close' the last worksheet and
'save to web for this database', but to iterate through all your worksheets,
hiding column 'J' and then *hide* the last sheet, you could do something
like

Sub HideJAndLastSheet()
Dim x As Integer, y As Integer
Dim wks As Worksheet
x = ThisWorkbook.Worksheets.Count
y = 1
For Each wks In ThisWorkbook.Worksheets
wks.Columns("J").Hidden = True
If y = x Then wks.Visible = xlSheetHidden
y = y + 1
Next wks
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
web: www.excelusergroup.org

 

[Mark G.]

While I am new to VBA, do I just resave this as a cut and paste? The save
for web is just an option I use under the file menu as I upload this info to
a website. I meant hide the last worksheet, not close.

 

[Mark G.]

What I got now seems to work fine and here it is:

Sub HideJAndLastSheet()
Dim x As Integer, y As Integer
Dim wks As Worksheet
x = ThisWorkbook.Worksheets.Count
y = 1
For Each wks In ThisWorkbook.Worksheets
wks.Columns("J").Hidden = True
If y = x Then wks.Visible = xlSheetHidden
y = y + 1
Next wks
ActiveWorkbook.Save
Range("C16").Select
Application.MacroOptions Macro:="HideJAndLastSheet", Description:= _
"Hide Column J in all and last worksheet.", ShortcutKey:="H"
Application.Run "'HW-Collection.xls'!HideJAndLastSheet"
Application.Run "'HW-Collection.xls'!HideJAndLastSheet"
Range("C10").Select
Application.Run "'HW-Collection.xls'!HideJAndLastSheet"
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Unimogs").Select
Range("D22").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Ferrari").Select
Range("E25").Select
Application.Run "'HW-Collection.xls'!HideJAndLastSheet"
Range("E24").Select
End Sub

But, now how do I set this up so that it will unhide those columns and
restore the last worksheet?

Thanks much.

 

[Mark G.]

Can anyone offer any help here?

What I got now seems to work fine and here it is:

Sub HideJAndLastSheet()
Dim x As Integer, y As Integer
Dim wks As Worksheet
x = ThisWorkbook.Worksheets.Count
y = 1
For Each wks In ThisWorkbook.Worksheets
wks.Columns("J").Hidden = True
If y = x Then wks.Visible = xlSheetHidden
y = y + 1
Next wks
ActiveWorkbook.Save
Range("C16").Select
Application.MacroOptions Macro:="HideJAndLastSheet", Description:= _
"Hide Column J in all and last worksheet.", ShortcutKey:="H"
Application.Run "'HW-Collection.xls'!HideJAndLastSheet"
Application.Run "'HW-Collection.xls'!HideJAndLastSheet"
Range("C10").Select
Application.Run "'HW-Collection.xls'!HideJAndLastSheet"
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Unimogs").Select
Range("D22").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Ferrari").Select
Range("E25").Select
Application.Run "'HW-Collection.xls'!HideJAndLastSheet"
Range("E24").Select
End Sub

But, now how do I set this up so that it will unhide those columns and
restore the last worksheet?

Thanks much.

 

[Mark G.]

Will try one more time to see if anyone here can assist???? Anyone??

 

[Gord Dibben]

Was Nick's macro not suitable or on the right track?

If not, why not?

You could post answers to Nick's questions to provide more detail of your needs.


Gord Dibben MS Excel MVP

 

[Mark G.]

Yeah...he got it right. When I run the macro he provided, it does hide the
column on all sheets and also hides the last sheet. But see, I need to be
able to run a similar macro that reverses that. How or what would I do to do
that? My objective here is to run the macro already provided, 'save to web',
then restore it back with all showing as it should. Suggestions or maybe
some kind of addition to the macro please? Thanks much for the help so far!

 

[Mark G.]

Anyone please????

Yeah...he got it right. When I run the macro he provided, it does hide the
column on all sheets and also hides the last sheet. But see, I need to be
able to run a similar macro that reverses that. How or what would I do to
do that? My objective here is to run the macro already provided, 'save to
web', then restore it back with all showing as it should. Suggestions or
maybe some kind of addition to the macro please? Thanks much for the help
so far!