column widths

  • Thread starter Thread starter Steven Cheng
  • Start date Start date
S

Steven Cheng

I have the following line code that copies the column
widths from one workbook to another but doesn't seem to be
working:

cwkb.activesheet.columns("A:AW").columnwidth =
thisworkbook.worksheets(1).columns("A:AW").columnwidth

I think that there is something simpler than this.
 
Steven,

How is it not working? Are you getting an error? Could it be that cwkb
workbook is not active? If it is, you probably don't need the cwkb
qualifier. If it isn't, you can't use Activesheet, but must use a sheet name
or index.
 
I think when Bob was testing, all the columnwidths in his "from" worksheet were
the same width.

When they were different widths, your code failed for me.

So if you have xl2k or higher, you could copy|paste special columnWidths:

Option Explicit
Sub testme03()

Dim cwkb As Workbook
Set cwkb = Workbooks("book2")

cwkb.ActiveSheet.Columns("A:AW").Copy
ThisWorkbook.Worksheets(1).Columns("A:AW").PasteSpecial _
Paste:=xlPasteColumnWidths
'paste:=8 'in xl2k--bug in the documentation

Application.CutCopyMode = False

End Sub

And if you're using xl97, I think you'll have to loop through the columns:

Option Explicit
Sub testme03()

Dim cwkb As Workbook
Dim myCol As Range
Set cwkb = Workbooks("book2")

For Each myCol In cwkb.ActiveSheet.Columns("A:AW")
ThisWorkbook.Worksheets(1).Columns(myCol.Address).ColumnWidth _
= myCol.ColumnWidth
Next myCol

End Sub

And I've never used activesheet as anything as a property of the application.
But it's also a property of a workbook and window. So that shouldn't cause a
problem. Although, I'd be scared that the activesheet wasn't the one I really
wanted.)
 
Back
Top