column widths

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.
 
B

Bob Phillips

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.
 
D

Dave Peterson

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.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top