Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Widths help ?
i knew it would be simple .. whereabouts in the code would it go ... Sub Split_Supplier_Codes() Dim ws1 As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("Customers") Set rng = Range("Customer") 'extract the list of Code Numbers ws1.Columns("A:A").Copy _ Destination:=Range("Z1") ws1.Columns("Z:Z").AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Range("X1"), Unique:=True r = Cells(Rows.Count, "X").End(xlUp).Row 'set up Criteria Area Range("Z1").Value = Range("A1").Value For Each c In Range("X2:X" & r) 'add the Code Numbers to the criteria area ws1.Range("Z2").Value = c.Value 'add new sheet (if required) 'and run advanced filter If WksExists(c.Value) Then Sheets(c.Value).Cells.Clear rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Customers").Range("Z1:Z2"), _ CopyToRange:=Sheets(c.Value).Range("A1"), _ Unique:=False Columns.AutoFit Else Set WSNew = Sheets.Add WSNew.Move After:=Worksheets(Worksheets.Count) WSNew.Name = c.Value rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Customers").Range("Z1:Z2"), _ CopyToRange:=WSNew.Range("A1"), _ Unique:=True End If Next ws1.Select ws1.Columns("Y:Z").Delete End Sub Function WksExists(wksName As String) As Boolean On Error Resume Next WksExists = CBool(Len(Worksheets(wksName).Name) 0) End Function Mike Fogleman Wrote: Have you tried Columns.AutoFit ? "GazMo" wrote in message ... I have a macro that creates new worksheets from a main table ... is there a line of code that will automatically set th width of columns to the widest value ? Thanks all ... -- GazMo ------------------------------------------------------------------------ GazMo's Profile: http://www.excelforum.com/member.php...o&userid=14610 View this thread http://www.excelforum.com/showthread...hreadid=272396 -- GazM ----------------------------------------------------------------------- GazMo's Profile: http://www.excelforum.com/member.php...fo&userid=1461 View this thread: http://www.excelforum.com/showthread.php?threadid=27239 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Widths help ?
hi
Try this Cells.entirecolumn.autofit Regards Frank -----Original Message----- i knew it would be simple .. whereabouts in the code would it go ... Sub Split_Supplier_Codes() Dim ws1 As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("Customers") Set rng = Range("Customer") 'extract the list of Code Numbers ws1.Columns("A:A").Copy _ Destination:=Range("Z1") ws1.Columns("Z:Z").AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Range("X1"), Unique:=True r = Cells(Rows.Count, "X").End(xlUp).Row 'set up Criteria Area Range("Z1").Value = Range("A1").Value For Each c In Range("X2:X" & r) 'add the Code Numbers to the criteria area ws1.Range("Z2").Value = c.Value 'add new sheet (if required) 'and run advanced filter If WksExists(c.Value) Then Sheets(c.Value).Cells.Clear rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Customers").Range("Z1:Z2") , _ CopyToRange:=Sheets(c.Value).Range("A1"), _ Unique:=False Columns.AutoFit Else Set WSNew = Sheets.Add WSNew.Move After:=Worksheets(Worksheets.Count) WSNew.Name = c.Value rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Customers").Range("Z1:Z2") , _ CopyToRange:=WSNew.Range("A1"), _ Unique:=True End If Next ws1.Select ws1.Columns("Y:Z").Delete End Sub Function WksExists(wksName As String) As Boolean On Error Resume Next WksExists = CBool(Len(Worksheets(wksName).Name) 0) End Function Mike Fogleman Wrote: Have you tried Columns.AutoFit ? "GazMo" wrote in message ... I have a macro that creates new worksheets from a main table ... is there a line of code that will automatically set the width of columns to the widest value ? Thanks all ... -- GazMo -------------------------------------------------------- ---------------- GazMo's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=14610 View this thread: http://www.excelforum.com/showthread...hreadid=272396 -- GazMo ---------------------------------------------------------- -------------- GazMo's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=14610 View this thread: http://www.excelforum.com/showthread...hreadid=272396 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Different widths for each column in a 100% stacked column chart | Charts and Charting in Excel | |||
Column Widths | Excel Discussion (Misc queries) | |||
Different column widths | Excel Worksheet Functions | |||
One column with different widths | New Users to Excel | |||
Column Widths help ? | Excel Programming |