There's lots of ways to accomplish this.
Some will depend on the version of Excel that you're using.
I have Excel 2007.
My first suggestion is:
Create a "template" sheet with the desired column widths and hide it.
Then, your macro can unhide the template,
select row 1
Copy to the clipboard
Loop through each sheet and paste-special, column widths
Hide your template
For this example, the template sheet is called "Col_Template"
(I know: How "descriptive")
then the macro looks like:
Option Explicit
Sub Fix_Cols()
Dim I, CurSheet
'
Application.ScreenUpdating = False
CurSheet = ActiveSheet.Name
Sheets("Col_Template").Visible = True
Sheets("Col_Template").Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Copy
For I = 1 To Sheets.Count
If (Sheets(I).Name <> "Col_Template") Then
Sheets(I).Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("B2").Select
End If
Next I
Sheets(CurSheet).Select
Sheets("Col_Template").Visible = False
Application.ScreenUpdating = True
End Sub
You COULD even call this macro from a BeforeSave event
so that it corrects the columns before saving the file.
=======================================
As an alternative, if the pastespecial doesn't work,
you can still create your template and compare the column widths of each sheet
to the template:
Sub Fix_Cols()
Dim I, CurSheet
Dim C
CurSheet = ActiveSheet.Name
For I = 1 To Sheets.Count
If (Sheets(I).Name <> "Col_Template") Then
For C = 1 To 100 'just checks the first 100 columns
If (Sheets(I).Cells(1, C).ColumnWidth <>
Sheets("Col_Template").Cells(1, C).ColumnWidth) Then
Sheets(I).Cells(1, C).ColumnWidth =
Sheets("Col_Template").Cells(1, C).ColumnWidth
End If
Next C
End If
Next I
Sheets(CurSheet).Select
Sheets("Col_Template").Visible = False
End Sub
Either way, these routines updated 100+ column widths in 50 sheets in 2-3
seconds.
let me know if either of these work for you.
Paul
----- Original Message ----
> From: iggy-mac <iggy-...@telinco.co.uk>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Tue, November 9, 2010 10:13:22 AM
> Subject: $$Excel-Macros$$ Column Widths
>
> First attempt at this, so hopefully this is how it works
>
> I have a spreadsheet with about 25 tabs and about 200 columns with
> lots of different widths. As its updated by many the widths get moved
> about. Before it is released, I run a macro to go through each column
> in each tab and restore it to its correct width ie along the lines of
> goto col a, set width 10, go to col B, set widthto 5 etc etc.
>
> It might nor be the best but it works fine until I have to add or
> delte colums. Thn I have to go through and amend the col width in
> VBa. Do-able but pain.
>
> I though I could set up a table that VBa could reference so that
> inserting a line or two would only require the table to be amended ie
>
> Col Width
> A 10
> B 5
> C 25
> etc
> etc
>
> Can anyone help with the VBa code both to do this and also another
> piece to identify the current widths.
>
> Thanks
>
> --
>----------------------------------------------------------------------------------
>-
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
