I'm trying to copy column widths from a sheet in a workbook that is to
be supplied as needed by the user, to a sheet in a new workbook
created by my Excel macro. In the sample workbook supplied by the user
for development, I find that the destination column widths are
visually much narrower than the source column widths, though the
column width number is the same for both.

I understand that the meaning of column width units comes from the
normal font of the default style of the workbook. But again, in both
cases that's the same -- Arial 10pt. (In Excel 2003, I'm looking at
Format | Style | Normal | Font)

Of course, I've also made sure that zoom is the same for both too --
100%.

For now, I've worked around this problem by doing a copy on the
worksheet object into the new workbook, and deleting what I don't
want. That gives me the same column widths.

But I'd still like to know what could be causing the different column
widths I was seeing. Any suggestions?


Thanks,

Greg

The standard font is changed through Tools > Options > General tab > Standard
font. Changing it only affects newly created wkbs. Your assesment as to the
cause is probably correct.

Regards,
Greg

"Greg Lovern" wrote:

> I'm trying to copy column widths from a sheet in a workbook that is to
> be supplied as needed by the user, to a sheet in a new workbook
> created by my Excel macro. In the sample workbook supplied by the user
> for development, I find that the destination column widths are
> visually much narrower than the source column widths, though the
> column width number is the same for both.
>
> I understand that the meaning of column width units comes from the
> normal font of the default style of the workbook. But again, in both
> cases that's the same -- Arial 10pt. (In Excel 2003, I'm looking at
> Format | Style | Normal | Font)
>
> Of course, I've also made sure that zoom is the same for both too --
> 100%.
>
> For now, I've worked around this problem by doing a copy on the
> worksheet object into the new workbook, and deleting what I don't
> want. That gives me the same column widths.
>
> But I'd still like to know what could be causing the different column
> widths I was seeing. Any suggestions?
>
>
> Thanks,
>
> Greg
>
>

On 30 Mar, 19:05, "Greg Lovern" <g...@gregl.net> wrote:
> I'm trying to copy column widths from a sheet in a workbook that is to
> be supplied as needed by the user, to a sheet in a new workbook
> created by my Excel macro. In the sample workbook supplied by the user
> for development, I find that the destination column widths are
> visually much narrower than the source column widths, though the
> column width number is the same for both.
>
> I understand that the meaning of column width units comes from the
> normal font of the default style of the workbook. But again, in both
> cases that's the same -- Arial 10pt. (In Excel 2003, I'm looking at
> Format | Style | Normal | Font)
>
> Of course, I've also made sure that zoom is the same for both too --
> 100%.
>
> For now, I've worked around this problem by doing a copy on the
> worksheet object into the new workbook, and deleting what I don't
> want. That gives me the same column widths.
>
> But I'd still like to know what could be causing the different column
> widths I was seeing. Any suggestions?
>
> Thanks,
>
> Greg

If you do this manually, the way to carry the column width would be
to click the whole column (by clicking on the column letter, say
column C) and copying and pasting that. If you copied C1:C1000, the
column width doesn't get carried over.

In vba, try defining the column as Range("C1").entirecolumn.copy.

i'm sure there is a shorter way, but i have used something like this before.

Option Explicit
Sub set_column_widths()
Dim arr() As Double
Dim i As Long
Dim x As Long
Dim z As Long
x = 0
For i = 1 To 26
ReDim Preserve arr(0 To i - 1)
arr(x) = Worksheets("Sheet1").Columns(i).ColumnWidth
x = x + 1
Next

For x = LBound(arr) To UBound(arr)
Worksheets("Sheet2").Columns(x + 1).ColumnWidth = arr(x)
Next
End Sub
--


Gary


"Greg Lovern" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm trying to copy column widths from a sheet in a workbook that is to
> be supplied as needed by the user, to a sheet in a new workbook
> created by my Excel macro. In the sample workbook supplied by the user
> for development, I find that the destination column widths are
> visually much narrower than the source column widths, though the
> column width number is the same for both.
>
> I understand that the meaning of column width units comes from the
> normal font of the default style of the workbook. But again, in both
> cases that's the same -- Arial 10pt. (In Excel 2003, I'm looking at
> Format | Style | Normal | Font)
>
> Of course, I've also made sure that zoom is the same for both too --
> 100%.
>
> For now, I've worked around this problem by doing a copy on the
> worksheet object into the new workbook, and deleting what I don't
> want. That gives me the same column widths.
>
> But I'd still like to know what could be causing the different column
> widths I was seeing. Any suggestions?
>
>
> Thanks,
>
> Greg
>

On Mar 31, 8:59 am, "jayray" <johnt...@gmail.com> wrote:
> If you do this manually, the way to carry thecolumnwidth would be
> to click the wholecolumn(by clicking on thecolumnletter, saycolumnC) and copying and pasting that. If you copied C1:C1000, thecolumnwidthdoesn't get carried over.
>
> In vba, try defining thecolumnas Range("C1").entirecolumn.copy.- Hide quoted text -


Thanks, but I've tried both of those and still have the same problem.

The destination column *does* get set to the same column width
*number* as the source column. The problem is that even though the two
columns then have the same column width *number*, the destination
column is *visually* much narrower than the source column.

(and both are on 100% zoom.)


Even if I manually set the destination column to the same column width
number as the source column, the destination column is still visually
much narrower than the source column.

Both have the same default font in their normal style -- Arial 10 --
which I understand to be the way that column width units are defined.


Thanks,

Greg

On Mar 31, 9:33 am, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
> i'm sure there is a shorter way, but i have used something like this before.
>
> Option Explicit
> Sub set_column_widths()
> Dim arr() As Double
> Dim i As Long
> Dim x As Long
> Dim z As Long
> x = 0
> For i = 1 To 26
> ReDim Preserve arr(0 To i - 1)
> arr(x) = Worksheets("Sheet1").Columns(i).ColumnWidth
> x = x + 1
> Next
>
> For x = LBound(arr) To UBound(arr)
> Worksheets("Sheet2").Columns(x + 1).ColumnWidth = arr(x)
> Next
> End Sub


Thanks, I tried that but still getthe same problem.

The destination column *does* get set to the same column width
*number* as the source column. The problem is that even though the two
columns then have the same column width *number*, the destination
column is *visually* much narrower than the source column.

(and both are on 100% zoom.)


Even if I manually set the destination column to the same column width
number as the source column, the destination column is still visually
much narrower than the source column.

Both have the same default font in their normal style -- Arial 10 --
which I understand to be the way that column width units are defined.


Thanks,

Greg

Did you check screen resolution settings? That would make a visual difference.

"Greg Lovern" wrote:

> On Mar 31, 9:33 am, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
> > i'm sure there is a shorter way, but i have used something like this before.
> >
> > Option Explicit
> > Sub set_column_widths()
> > Dim arr() As Double
> > Dim i As Long
> > Dim x As Long
> > Dim z As Long
> > x = 0
> > For i = 1 To 26
> > ReDim Preserve arr(0 To i - 1)
> > arr(x) = Worksheets("Sheet1").Columns(i).ColumnWidth
> > x = x + 1
> > Next
> >
> > For x = LBound(arr) To UBound(arr)
> > Worksheets("Sheet2").Columns(x + 1).ColumnWidth = arr(x)
> > Next
> > End Sub
>
>
> Thanks, I tried that but still getthe same problem.
>
> The destination column *does* get set to the same column width
> *number* as the source column. The problem is that even though the two
> columns then have the same column width *number*, the destination
> column is *visually* much narrower than the source column.
>
> (and both are on 100% zoom.)
>
>
> Even if I manually set the destination column to the same column width
> number as the source column, the destination column is still visually
> much narrower than the source column.
>
> Both have the same default font in their normal style -- Arial 10 --
> which I understand to be the way that column width units are defined.
>
>
> Thanks,
>
> Greg
>
>

In case anyone is interested, I've found the problem:

The source sheet had View Formulas turned on (Tools | Options | View |
Window Options | Formulas), which widens the columns. The destination
sheet did not have View Formulas turned on.

I hadn't noticed because that sheet didn't have any formulas. I have
no idea why View Formulas was turned on in the sample provided by the
user.

So the reason my workaround worked was because by doing a copy on the
source worksheet object into the destination workbook, I also copied
over the View Formulas setting.

Greg


On Mar 30, 4:05 pm, "Greg Lovern" <g...@gregl.net> wrote:
> I'm trying tocopycolumnwidths from a sheet in a workbook that is to
> be supplied as needed by the user, to a sheet in a new workbook
> created by myExcelmacro. In the sample workbook supplied by the user
> for development, I find that the destinationcolumnwidths are
> visually much narrower than the sourcecolumnwidths, though thecolumnwidthnumberis the same for both.
>
> I understand that the meaning ofcolumnwidthunits comes from the
> normal font of thedefaultstyleof the workbook. But again, in both
> cases that's the same --Arial10pt. (InExcel2003, I'm looking at
> Format |Style| Normal | Font)
>
> Of course, I've also made sure that zoom is the same for both too --
> 100%.
>
> For now, I've worked around thisproblemby doing acopyon the
> worksheet object into the new workbook, and deleting what I don't
> want. That gives me the samecolumnwidths.
>
> But I'd still like to know what could be causing the differentcolumn
> widths I was seeing. Any suggestions?
>
> Thanks,
>
> Greg

yes, i was interested - thanks for reporting the solution.
additionally, anybody who searches the newsgroup in the future & finds
your answer will greatly appreciate it, too!
susan


On Apr 6, 2:41 pm, "Greg Lovern" <g...@gregl.net> wrote:
> In case anyone is interested, I've found the problem:
>
> The source sheet had View Formulas turned on (Tools | Options | View |
> Window Options | Formulas), which widens the columns. The destination
> sheet did not have View Formulas turned on.
>
> I hadn't noticed because that sheet didn't have any formulas. I have
> no idea why View Formulas was turned on in the sample provided by the
> user.
>
> So the reason my workaround worked was because by doing a copy on the
> source worksheet object into the destination workbook, I also copied
> over the View Formulas setting.
>
> Greg
>
> On Mar 30, 4:05 pm, "Greg Lovern" <g...@gregl.net> wrote:
>
>
>
> > I'm trying tocopycolumnwidths from a sheet in a workbook that is to
> > be supplied as needed by the user, to a sheet in a new workbook
> > created by myExcelmacro. In the sample workbook supplied by the user
> > for development, I find that the destinationcolumnwidths are
> > visually much narrower than the sourcecolumnwidths, though thecolumnwidthnumberis the same for both.
>
> > I understand that the meaning ofcolumnwidthunits comes from the
> > normal font of thedefaultstyleof the workbook. But again, in both
> > cases that's the same --Arial10pt. (InExcel2003, I'm looking at
> > Format |Style| Normal | Font)
>
> > Of course, I've also made sure that zoom is the same for both too --
> > 100%.
>
> > For now, I've worked around thisproblemby doing acopyon the
> > worksheet object into the new workbook, and deleting what I don't
> > want. That gives me the samecolumnwidths.
>
> > But I'd still like to know what could be causing the differentcolumn
> > widths I was seeing. Any suggestions?
>
> > Thanks,
>
> > Greg- Hide quoted text -
>
> - Show quoted text -