Sleeper: Detect and change column width

[ilyaskazi]

workbook contains following sheets:
LONDON1, LONDON2, DELHI1, DELHI2, FRANKFURT, PARIS

sheet may contain by any names and in any nos.

detect all columns width of sheet "LONDON1" and apply to "LONDON2" and likewise to all containing sheets, except non-matching sheets (FRANKFURT and PARIS)

[BlueCactus]

A couple of functions to get you started:


Worksheets.Count
...gives you the number of worksheets

Worksheets(n).Name
...gives you the name of Worksheet n, where n is from 1 to Worksheets.Count

Worksheets(n).Columns(c).Width
...gives you the width of Column c in Worksheet n, where Column A would be c = 1

You can also refer to a sheet by name rather than index number:

Worksheets("name").Columns(1).Width
where name is the Worksheet name. (Must be enclosed in quotes.)

To get the base name of Worksheet n, for example 'LONDON' from 'LONDON2' use something like:

Dim n As Integer, i As Integer, baseName As String
n = 1
If IsNumeric(Right(Worksheets(n).Name, 1)) Then
i = 0
Do While Not IsNumeric(Mid(Worksheets(n).Name, i + 1, 1))
i = i + 1
Loop
baseName = Left(Worksheets(n).Name, i)
Else
baseName = Worksheets(n).Name
End If


BTW, I can guarantee you that there is an easier way of doing that last section, but this way didn't require me to learn anything new! :giggle