Pasting to new worksheet with formats

[scott]

I have a master report of customer sales. In column 10 is the sales
region. I am trying to create individual sheets by region. I have been
using Filtering to only get the data.
1)Is there a better way? and

2) Is there a way to copy the data and the formats,including column
widths. Every time I try to Paste Special Paste:xlColumnWidth I get errors.


Sub Regions()

Set lastcell = Cells.SpecialCells(xlLastCell)
ActiveSheet.Name = "ALL"
Worksheets.Add.Move after:=Worksheets(Worksheets.count)
ActiveSheet.Name = "EAST"
Worksheets.Add.Move after:=Worksheets(Worksheets.count)
ActiveSheet.Name = "CENTRAL"
Worksheets.Add.Move after:=Worksheets(Worksheets.count)
ActiveSheet.Name = "WEST"
Worksheets("ALL").Activate



Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:="EAST"
Range("A1", lastcell).Select
Selection.Copy Worksheets("EAST").Range("A1")


Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:="CENTRAL"
Range("A1", lastcell).Select
Selection.Copy Worksheets("CENTRAL").Range("A1")

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:="WEST"
Range("A1", lastcell).Select
Selection.Copy Worksheets("WEST").Range("A1")


End Sub


Scott

 

[Tom Ogilvy]

Not sure why you would think applying a filter is a bad way. If you have
working code, what is the struggle?

If you are using xl 2000, the variable for columnwidth wasn't defined. You
need to use 8 as the argument as I recall.

in xl97, this option was not available (added in xl2000)