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.

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
DTHIS
web: www.nickhodge.co.uk
web: www.excelusergroup.org




"Mark G." wrote in message
...
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.

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.


"Nick Hodge" wrote in message
...
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
DTHIS
web: www.nickhodge.co.uk
web: www.excelusergroup.org




"Mark G." wrote in message
...
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.

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.


"Nick Hodge" wrote in message
...
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
DTHIS
web: www.nickhodge.co.uk
web: www.excelusergroup.org




"Mark G." wrote in message
...
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.

Can anyone offer any help here?


"Mark G." wrote in message
...
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.


"Nick Hodge" wrote in message
...
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
DTHIS
web: www.nickhodge.co.uk
web: www.excelusergroup.org




"Mark G." wrote in message
...
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.

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



"Mark G." wrote in message
...
Can anyone offer any help here?


"Mark G." wrote in message
...
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.


"Nick Hodge" wrote in message
...
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
DTHIS
web: www.nickhodge.co.uk
web: www.excelusergroup.org




"Mark G." wrote in message
...
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.

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

On Fri, 25 Jan 2008 00:42:13 -0800, "Mark G." wrote:

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



"Mark G." wrote in message
...
Can anyone offer any help here?


"Mark G." wrote in message
...
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.


"Nick Hodge" wrote in message
...
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
DTHIS
web: www.nickhodge.co.uk
web: www.excelusergroup.org




"Mark G." wrote in message
...
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.