How do I write an array to include all worksheets in a workbook?

  • Thread starter Thread starter Jodie
  • Start date Start date
J

Jodie

I am trying to add to a marco to auto fit columns A through H in all
worksheets. How would I add this so it does not matter how many tabs are in
the workbook as it will continually change?
 
hi,

Right click any of your sheet tab, view code and paste this code in and run it

Sub sonic()
For x = 1 To Worksheets.Count
Sheets(x).Columns("A:H").AutoFit
Next
End Sub

Mike
 
Just out of interest, why did you suggest making this a sheet subroutine
rather than putting it in a general module?
best wsihes
 
Bernard,

Several reasons:-

It will work

I guess (and apologise to the OP if i'm wrong) that the OP is very
inexperienced and entering worksheet code utilises less steps than entering a
general module. Three mouse mouse clicks and the code is in.

It's my habit and preferred way of doing it 'provided' I'm not reading from
or writing to sheets other than the one that contains the code. Although
there's no reason why worksheet code couldn't work too if doing that.

I'm aware of the convention that worksheet modules are for event code but
have never heard a compelling argument that it's anything other than that, 'a
convention'.

Mike
 
Several reasons:-

It will work
Click to expand...

.... unless some innocent user deletes this special purpose sheet with
the implicit general functionality.

Why not just say 1. Press ALT + F11 2. Insert a new module 3. Copy the
code into the new module 4. Go back to worksheet and use it?

Regards,
Bernd
 
As stated

It's my habit and preferred way of doing it 'provided' I'm not reading from
or writing to sheets other than the one that contains the code. Although
there's no reason why worksheet code couldn't work too if doing that

If you believe this is fundamentally incorrect provide a compelling argument
as to why.

Before you reply consider why Microsoft provide a 'general' option in the
dropdown of worksheet modules if entering something other than event code is
inappropriate.

Mike
 
If you believe this is fundamentally incorrect provide a compelling argument
as to why.
Before you reply consider why Microsoft provide a 'general' option in the
dropdown of worksheet modules if entering something other than event code is
inappropriate.
Click to expand...

Will just give it a try..

---Private functions/procedures which are to be called from the sheet events
and are only applicable to that particular sheet are usually placed in that
module itself. (can either be a worksheet or a chart) so that if the sheet is
copied/moved to another workbook these functions/procedures would
automatically be saved along with the sheet. For example from the sheet
events if you need to do repeated tasks; like the below which are only
applicable for that particular sheet; these are ideally placed in the sheet
module.

--- It is generally accepted as good programming practice that only
procedures/functions which are used only within that module be placed in that
and others be placed in the general module. (Like placing a worksheet change
event for a ***particular*** sheet in the 'This Workbook'). In the OP's case
since the macro works on all the worksheets of the workbook it would be
considered in-appropriate to place this in sheet module..

If this post helps click Yes
 
Mike; I re-read what I posted and the below two sentences found to be
confusing ...
For example from the sheet events if you need to do repeated tasks; like the >below
Click to expand...
I am not referring to the OPs requirement but way planning to copy few
samples of functions/procedures which are applicable only to that sheet;
later left that idea...Hope you can understand what would be those
(Like placing a worksheet change event for a ***particular*** sheet in the 'This >Workbook').
Click to expand...
should have also added that doing the above would not be considered as not a
good practice.

If this post helps click Yes
 
Good to know
Bernard

Mike H said:
Bernard,

Several reasons:-

It will work

I guess (and apologise to the OP if i'm wrong) that the OP is very
inexperienced and entering worksheet code utilises less steps than
entering a
general module. Three mouse mouse clicks and the code is in.

It's my habit and preferred way of doing it 'provided' I'm not reading
from
or writing to sheets other than the one that contains the code. Although
there's no reason why worksheet code couldn't work too if doing that.

I'm aware of the convention that worksheet modules are for event code but
have never heard a compelling argument that it's anything other than that,
'a
convention'.

Mike
Click to expand...
 
Back
Top