Issue with marco for entire workbook
 

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.

Issue with marco for entire workbook
 

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.

Issue with marco for entire workbook
 

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.

Issue with marco for entire workbook
 

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.

Issue with marco for entire workbook
 

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.

Issue with marco for entire workbook
 

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.

Issue with marco for entire workbook
 

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.

Issue with marco for entire workbook
 

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!


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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.

Issue with marco for entire workbook
 

Anyone please????



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


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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.