Sort to Various Sheets in Workbook
 

I have a workseet (Variance Analysis) in a workbook & want a Macro to
run through the information on this workseet sorting by Div # (Column
C) and then copying the entire row the correpsonding Division Sheet
within the Workbook. I have prepared a macro that was able to sort
the information but will not paste it the division sheet.. Below is my
attempt, does anyone have any suggestions?


Sheets("Variance Analysis").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="0005"
Range("A2:e2").Select
Range(Selection, Selection.End(x1Down)).Select
Selection.Copy
Sheets("Div 5").Select
Range("b14").Select
ActiveSheet.Paste
Columns("c:F").Select
Columns("c:F").EntireColumn.AutoFit
Sheets("Variance Analysis").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=5, Criteria1:="0102"
Range("A2:d2").Select
Range(Selection, Selection.End(x1Down)).Select
Selection.Copy
Sheets("Div 102").Select
Range("b14").Select
ActiveSheet.Paste
Columns("c:F").Select
Columns("c:F").EntireColumn.AutoFit
Sheets("Variance Analysis").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=5, Criteria1:="0109"
Range("A2:d2").Select
Range(Selection, Selection.End(x1Down)).Select
Selection.Copy
Sheets("Div 109").Select
Range("b14").Select
ActiveSheet.Paste
Columns("c:F").Select
Columns("c:F").EntireColumn.AutoFit
Sheets("Variance Analysis").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=5, Criteria1:="0112"
Range("A2:d2").Select
Range(Selection, Selection.End(x1Down)).Select
Selection.Copy
Sheets("Div 112").Select
Range("b14").Select
ActiveSheet.Paste
Columns("c:F").Select
Columns("c:F").EntireColumn.AutoFit


Thanks

Steve B



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Sort to Various Sheets in Workbook
 

Debra Dalgleish has a nice technique for separating data into different sheets
at:

http://www.contextures.com/excelfiles.html#Filter
(look for: Create New Sheets from Filtered List
and: Update Sheets from Master)

(And watchout in your code: xldown is not ex-one-down, it's ex-ell-down.)

STEVEB wrote:

I have a workseet (Variance Analysis) in a workbook & want a Macro to
run through the information on this workseet sorting by Div # (Column
C) and then copying the entire row the correpsonding Division Sheet
within the Workbook. I have prepared a macro that was able to sort
the information but will not paste it the division sheet.. Below is my
attempt, does anyone have any suggestions?

Sheets("Variance Analysis").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="0005"
Range("A2:e2").Select
Range(Selection, Selection.End(x1Down)).Select
Selection.Copy
Sheets("Div 5").Select
Range("b14").Select
ActiveSheet.Paste
Columns("c:F").Select
Columns("c:F").EntireColumn.AutoFit
Sheets("Variance Analysis").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=5, Criteria1:="0102"
Range("A2:d2").Select
Range(Selection, Selection.End(x1Down)).Select
Selection.Copy
Sheets("Div 102").Select
Range("b14").Select
ActiveSheet.Paste
Columns("c:F").Select
Columns("c:F").EntireColumn.AutoFit
Sheets("Variance Analysis").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=5, Criteria1:="0109"
Range("A2:d2").Select
Range(Selection, Selection.End(x1Down)).Select
Selection.Copy
Sheets("Div 109").Select
Range("b14").Select
ActiveSheet.Paste
Columns("c:F").Select
Columns("c:F").EntireColumn.AutoFit
Sheets("Variance Analysis").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=5, Criteria1:="0112"
Range("A2:d2").Select
Range(Selection, Selection.End(x1Down)).Select
Selection.Copy
Sheets("Div 112").Select
Range("b14").Select
ActiveSheet.Paste
Columns("c:F").Select
Columns("c:F").EntireColumn.AutoFit

Thanks

Steve B

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson