This is only the Fourth Time I ever worked with Excel, so please excuse my
ignorance.

I have four columns I am creating in a new WorkBook.

Two have very long descriptions and look terrible. I come from a huge
Worksheet so I am creating a more manageable new WorkBook.

My AutoFit is not working?

Any body know what I am doing wrong. Part of my exisiting code is from the
users group already.

Here is my code.

Dim wkst As Worksheet ' Current Sheet
Dim wb As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim rng As Range
Dim SelCol As String
Dim ColNum As Long

Set wkst = ActiveSheet

SelCol = InputBox("Comment Please:!")
ColNum = Columns(SelCol & ":" & SelCol).Column
SelCol = "A1," & SelCol & "1"

Set wb = Workbooks.Add(1)
Set wslb = wb.Worksheets(1)

With wkst
If .AutoFilterMode Then .AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
With rng
.AutoFilter Field:=ColNum, Criteria1:="<"
.Columns(ColNum).Copy wslb.Columns(1).Cells(1)
.Columns(2).Copy wslb.Columns(2).Cells(1)
.Columns(254).Copy wslb.Columns(3).Cells(1)
End With
End With

wslb.Range("A1:E1").Columns.AutoFit * AutoFit Not Working

Thanks to the Group

J. Q.

What is the error message you are getting?

"John Quinn" wrote:

This is only the Fourth Time I ever worked with Excel, so please excuse my
ignorance.

I have four columns I am creating in a new WorkBook.

Two have very long descriptions and look terrible. I come from a huge
Worksheet so I am creating a more manageable new WorkBook.

My AutoFit is not working?

Any body know what I am doing wrong. Part of my exisiting code is from the
users group already.

Here is my code.

Dim wkst As Worksheet ' Current Sheet
Dim wb As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim rng As Range
Dim SelCol As String
Dim ColNum As Long

Set wkst = ActiveSheet

SelCol = InputBox("Comment Please:!")
ColNum = Columns(SelCol & ":" & SelCol).Column
SelCol = "A1," & SelCol & "1"

Set wb = Workbooks.Add(1)
Set wslb = wb.Worksheets(1)

With wkst
If .AutoFilterMode Then .AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
With rng
.AutoFilter Field:=ColNum, Criteria1:="<"
.Columns(ColNum).Copy wslb.Columns(1).Cells(1)
.Columns(2).Copy wslb.Columns(2).Cells(1)
.Columns(254).Copy wslb.Columns(3).Cells(1)
End With
End With

wslb.Range("A1:E1").Columns.AutoFit * AutoFit Not Working

Thanks to the Group

J. Q.

Barb;
Thanks for getting back to me.

I do not get an error message, it just does not work!

Thanks Again

J.Q.

"Barb Reinhardt" wrote:

What is the error message you are getting?

"John Quinn" wrote:

This is only the Fourth Time I ever worked with Excel, so please excuse my
ignorance.

I have four columns I am creating in a new WorkBook.

Two have very long descriptions and look terrible. I come from a huge
Worksheet so I am creating a more manageable new WorkBook.

My AutoFit is not working?

Any body know what I am doing wrong. Part of my exisiting code is from the
users group already.

Here is my code.

Dim wkst As Worksheet ' Current Sheet
Dim wb As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim rng As Range
Dim SelCol As String
Dim ColNum As Long

Set wkst = ActiveSheet

SelCol = InputBox("Comment Please:!")
ColNum = Columns(SelCol & ":" & SelCol).Column
SelCol = "A1," & SelCol & "1"

Set wb = Workbooks.Add(1)
Set wslb = wb.Worksheets(1)

With wkst
If .AutoFilterMode Then .AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
With rng
.AutoFilter Field:=ColNum, Criteria1:="<"
.Columns(ColNum).Copy wslb.Columns(1).Cells(1)
.Columns(2).Copy wslb.Columns(2).Cells(1)
.Columns(254).Copy wslb.Columns(3).Cells(1)
End With
End With

wslb.Range("A1:E1").Columns.AutoFit * AutoFit Not Working

Thanks to the Group

J. Q.

On Jul 8, 12:50 pm, John Quinn
wrote:
This is only the Fourth Time I ever worked with Excel, so please excuse my
ignorance.

I have four columns I am creating in a new WorkBook.

Two have very long descriptions and look terrible. I come from a huge
Worksheet so I am creating a more manageable new WorkBook.

My AutoFit is not working?

Any body know what I am doing wrong. Part of my exisiting code is from the
users group already.

Here is my code.

Dim wkst As Worksheet ' Current Sheet
Dim wb As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim rng As Range
Dim SelCol As String
Dim ColNum As Long

Set wkst = ActiveSheet

SelCol = InputBox("Comment Please:!")
ColNum = Columns(SelCol & ":" & SelCol).Column
SelCol = "A1," & SelCol & "1"

Set wb = Workbooks.Add(1)
Set wslb = wb.Worksheets(1)

With wkst
If .AutoFilterMode Then .AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
With rng
.AutoFilter Field:=ColNum, Criteria1:="<"
.Columns(ColNum).Copy wslb.Columns(1).Cells(1)
.Columns(2).Copy wslb.Columns(2).Cells(1)
.Columns(254).Copy wslb.Columns(3).Cells(1)
End With
End With

wslb.Range("A1:E1").Columns.AutoFit * AutoFit Not Working

Thanks to the Group

J. Q.

Hi,

maybe at the end try wslb.Columns("a:e").AutoFit instead of
wslb.Range("A1:E1").Columns.AutoFit

regards

Thanks for getting back to me!

Same results, no change and no halts!

J.Q.

"iCalculate" wrote:

On Jul 8, 12:50 pm, John Quinn
wrote:
This is only the Fourth Time I ever worked with Excel, so please excuse my
ignorance.

I have four columns I am creating in a new WorkBook.

Two have very long descriptions and look terrible. I come from a huge
Worksheet so I am creating a more manageable new WorkBook.

My AutoFit is not working?

Any body know what I am doing wrong. Part of my exisiting code is from the
users group already.

Here is my code.

Dim wkst As Worksheet ' Current Sheet
Dim wb As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim rng As Range
Dim SelCol As String
Dim ColNum As Long

Set wkst = ActiveSheet

SelCol = InputBox("Comment Please:!")
ColNum = Columns(SelCol & ":" & SelCol).Column
SelCol = "A1," & SelCol & "1"

Set wb = Workbooks.Add(1)
Set wslb = wb.Worksheets(1)

With wkst
If .AutoFilterMode Then .AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
With rng
.AutoFilter Field:=ColNum, Criteria1:="<"
.Columns(ColNum).Copy wslb.Columns(1).Cells(1)
.Columns(2).Copy wslb.Columns(2).Cells(1)
.Columns(254).Copy wslb.Columns(3).Cells(1)
End With
End With

wslb.Range("A1:E1").Columns.AutoFit * AutoFit Not Working

Thanks to the Group

J. Q.

Hi,

maybe at the end try wslb.Columns("a:e").AutoFit instead of
wslb.Range("A1:E1").Columns.AutoFit

regards

If those columns are set to wrap text (format|Cells|alignment tab), then maybe
the autofit is working perfectly fine.

You may want to make sure that wrap text is off.

If you autofit the columns manually do you get a different result?

You could try something like:

with wslb.Range("A1:E1").entirecolumn
.columnwidth = 255
.autofit
end with

This won't look pretty if any of those columns are empty!

John Quinn wrote:

This is only the Fourth Time I ever worked with Excel, so please excuse my
ignorance.

I have four columns I am creating in a new WorkBook.

Two have very long descriptions and look terrible. I come from a huge
Worksheet so I am creating a more manageable new WorkBook.

My AutoFit is not working?

Any body know what I am doing wrong. Part of my exisiting code is from the
users group already.

Here is my code.

Dim wkst As Worksheet ' Current Sheet
Dim wb As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim rng As Range
Dim SelCol As String
Dim ColNum As Long

Set wkst = ActiveSheet

SelCol = InputBox("Comment Please:!")
ColNum = Columns(SelCol & ":" & SelCol).Column
SelCol = "A1," & SelCol & "1"

Set wb = Workbooks.Add(1)
Set wslb = wb.Worksheets(1)

With wkst
If .AutoFilterMode Then .AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
With rng
.AutoFilter Field:=ColNum, Criteria1:="<"
.Columns(ColNum).Copy wslb.Columns(1).Cells(1)
.Columns(2).Copy wslb.Columns(2).Cells(1)
.Columns(254).Copy wslb.Columns(3).Cells(1)
End With
End With

wslb.Range("A1:E1").Columns.AutoFit * AutoFit Not Working

Thanks to the Group

J. Q.

--

Dave Peterson

Dave;

That did the trick!

Thanks Again, that is the second time you solved a problem for me!

J.Q.

"Dave Peterson" wrote:

If those columns are set to wrap text (format|Cells|alignment tab), then maybe
the autofit is working perfectly fine.

You may want to make sure that wrap text is off.

If you autofit the columns manually do you get a different result?

You could try something like:

with wslb.Range("A1:E1").entirecolumn
.columnwidth = 255
.autofit
end with

This won't look pretty if any of those columns are empty!

John Quinn wrote:

This is only the Fourth Time I ever worked with Excel, so please excuse my
ignorance.

I have four columns I am creating in a new WorkBook.

Two have very long descriptions and look terrible. I come from a huge
Worksheet so I am creating a more manageable new WorkBook.

My AutoFit is not working?

Any body know what I am doing wrong. Part of my exisiting code is from the
users group already.

Here is my code.

Dim wkst As Worksheet ' Current Sheet
Dim wb As Workbook
Dim wslb As Worksheet ' Work Sheet
Dim rng As Range
Dim SelCol As String
Dim ColNum As Long

Set wkst = ActiveSheet

SelCol = InputBox("Comment Please:!")
ColNum = Columns(SelCol & ":" & SelCol).Column
SelCol = "A1," & SelCol & "1"

Set wb = Workbooks.Add(1)
Set wslb = wb.Worksheets(1)

With wkst
If .AutoFilterMode Then .AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
With rng
.AutoFilter Field:=ColNum, Criteria1:="<"
.Columns(ColNum).Copy wslb.Columns(1).Cells(1)
.Columns(2).Copy wslb.Columns(2).Cells(1)
.Columns(254).Copy wslb.Columns(3).Cells(1)
End With
End With

wslb.Range("A1:E1").Columns.AutoFit * AutoFit Not Working

Thanks to the Group

J. Q.

--

Dave Peterson