Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting to new worksheet with formats
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting to new worksheet with formats
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) -- Regards, Tom Ogilvy scott wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting formats and values without formulas | Excel Worksheet Functions | |||
RESTRICT PASTING FORMATS | Excel Discussion (Misc queries) | |||
holding worksheet formats when copying/pasting | Excel Discussion (Misc queries) | |||
pasting links and formats | Excel Discussion (Misc queries) | |||
Formats when cutting and pasting | Excel Discussion (Misc queries) |