Jay,
Here's all my code.
I use this to move all the Salesmans data to their respective Sheet.
I'm more than sure it can be cleaned up to run better.
Being a Novice, I will get better as time goes on.
I'm going to try and use your suggestion provided I can understand how it works.
Here's the code (hope it doesn't mess up the MESSAGE Board).
Sub CommReports()
'
' CommReports Macro
' Macro recorded 4/19/2002 by Mike Zaccardo
'
'
Application.ScreenUpdating = False 'This hides the Physical running Process of the Macro.
Application.Goto Reference:="R2C2" 'Places the Cursor at B2.
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=ActiveCell.Offset(0, 15).Range("A1"), Order1:= _
xlAscending, Key2:=ActiveCell.Offset(0, 1).Range("A1"), Order2:=xlAscending _
, Key3:=ActiveCell, Order3:=xlAscending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom 'We've just Sorted the entire Worsheet by COMM CODE.
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.AutoFilter 'Turns the AutoFilter "ON".
Application.Goto Reference:="R1C2"
Selection.End(xlToRight).Select 'Places the Cursor at R1.
Selection.AutoFilter Field:=17, Criteria1:="=026", Operator:=xlOr, _
Criteria2:="=009"
'The Previous Code Goes to Field 17 and only shows 026 which is Clarks COMM CODE.
Application.Goto Reference:="R1C2" 'GOING FROM HERE TO THE NEXT LINE IS WHERE I'M HAVING MY PROBLEM.
ActiveCell.Offset(39, 0).Range("A1").Select 'NEED TO GET TO HERE ANOTHER WAY. NEXT MONTH IT MAY BE (50,0).
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy 'The previous 4 lines copies over only the Visible Cells to Salesman's Sheet.
Selection.SpecialCells(xlCellTypeVisible).Select 'Sets up for Deletion of the Visible Rows Only.
Sheets("CLARK #026 #009").Select 'Then we select Only the Info for Clark GOTO his Sheet for Pasting.
Application.Goto Reference:="R10C2"
ActiveSheet.Paste
Application.CutCopyMode = False
Application.Goto Reference:="R10C2"
Application.Goto Reference:="R9C8"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(8, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "TOTAL INV" & Chr(10) & "Payments"
With ActiveCell.Characters(Start:=1, Length:=18).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
'Data Xferred to Joe's Sheet.
'Next Salesman (DeSimone).
Sheets("PAI EFP MERGED").Select 'We return to the First Sheet in Workbook.
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete 'All Rows are deleted.
Application.Goto Reference:="R1C2"
ActiveWindow.ScrollColumn = 6
Selection.AutoFilter Field:=17
Selection.End(xlToRight).Select
Selection.AutoFilter Field:=17
Range("A1").Select
Selection.End(xlToRight).Select
Selection.AutoFilter Field:=17, Criteria1:="=027", Operator:=xlOr, _
Criteria2:="=006"
'DeSimone Data Criteria.
Application.Goto Reference:="R1C2"
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("DESIMONE #027 #006").Select
Application.Goto Reference:="R10C2"
ActiveSheet.Paste
Application.CutCopyMode = False
Application.Goto Reference:="R9C8"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(8, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "TOTAL INV" & Chr(10) & "Payments"
With ActiveCell.Characters(Start:=1, Length:=18).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Sheets("PAI EFP MERGED").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
'DeSimone Data XFerred.
'Next Salesman (Salicondro).
ActiveWindow.ScrollColumn = 6
Selection.AutoFilter Field:=17
Selection.End(xlToRight).Select
Selection.AutoFilter Field:=17
Range("A1").Select
Selection.AutoFilter Field:=17
Selection.AutoFilter Field:=17, Criteria1:="=029", Operator:=xlOr, _
Criteria2:="=008"
'Salicondro Data Criteria.
Application.Goto Reference:="R1C2"
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("SALICONDRO #029 & #008").Select
Application.Goto Reference:="R10C2"
ActiveSheet.Paste
Application.CutCopyMode = False
Application.Goto Reference:="R9C8"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(8, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "TOTAL INV" & Chr(10) & "Payments"
With ActiveCell.Characters(Start:=1, Length:=18).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
ActiveCell.Offset(0, -3).Columns("A:A").EntireColumn.Select
Selection.Columns.AutoFit
ActiveCell.Offset(8, 0).Range("A1").Select
Application.Goto Reference:="R10C2"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
With Selection
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveCell.Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveWorkbook.Save
'All Sheets Updated.
Sheets("PAI EFP MERGED").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.ClearContents
Selection.AutoFilter Field:=17
'Comin to the END.
Application.Goto Reference:="R1C2"
Selection.AutoFilter
Sheets("PAIINV Formatted").Select
Application.Goto Reference:="R1C1"
ActiveCell.Columns("A:A").EntireColumn.Select
'Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
ActiveCell.Select
Sheets("EFPEXT Formatted").Select
Application.Goto Reference:="R1C1"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Select
Sheets("PAI EFP MERGED").Select
ActiveCell.Select
'THIS CODE ADDS 5 BLANK LINES AND ADDS A NEW HEADER.
Sheets("CLARK #026 #009").Select
Application.Goto Reference:="R10C1"
ActiveCell.Rows("1:5").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Select
Sheets("DESIMONE #027 #006").Select
Application.Goto Reference:="R10C1"
ActiveCell.Rows("1:5").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Select
Sheets("SALICONDRO #029 & #008").Select
Application.Goto Reference:="R10C1"
ActiveCell.Rows("1:5").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(4, 1).Range("A1:R1").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "UNCOLLECTED Invoice Detail"
With ActiveCell.Characters(Start:=1, Length:=26).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 1
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.InsertIndent 1
ActiveCell.Select
Sheets("DESIMONE #027 #006").Select
ActiveCell.Offset(4, 1).Range("A1:R1").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "UNCOLLECTED Invoice Detail"
With ActiveCell.Characters(Start:=1, Length:=26).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 1
.ShrinkToFit = False
.MergeCells = False
End With
Sheets("DESIMONE #027 #006").Select
ActiveCell.Range("A1:R2").Select
Selection.Copy
Sheets("CLARK #026 #009").Select
ActiveCell.Offset(3, 1).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Select
Sheets("PAI EFP MERGED").Select
Sheets("PAIINV Formatted").Select
Sheets("PAI EFP MERGED").Select
ActiveCell.Select
Application.ScreenUpdating = True
End With
ActiveWorkbook.Save
End Sub
*****
How do I read your coding?
Dim DataRng As Range, etc, etc.,
ending with,
DataRng1.End(xlToRight).
*****