Jay Petrulis or Anyone.

[Zac]

This is a carryover from yesterday.
I'm going to list my code and I hope
you will see where my problem is.

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 (which is Col "Q" the 16th letter of the Alphabet duh!).
'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


After using the Data Filter Row #1. How can
I go to the next visible line and CTRL+Shift+End, and Copy?
Going here is my problem:
ActiveCell.Offset(39, 0).Range("A1").Select

If I use the same code in original macro I
could ignore data to be copied. Especially since it remembers (39,0) If my data runs from (20:50), I'm "toast".

Please get back to me and I'll try to
explain further if necessary.


Zac

[Jay Petrulis]

Try the following with variable length data sets and report the results.

Sub test()
Dim DataRng As Range, DataRng1 As Range
Dim DataRng2 As Range, DataRng3 As Range

With Sheets("Sheet1")
    Set DataRng = .UsedRange
    Set DataRng1 = .Range("A2", .Range("A2").End(xlDown))
    Set DataRng2 = .Range(DataRng1, DataRng1.End(xlToRight))
    
    With .Range("A1").CurrentRegion
        .Sort Key1:=.Range("Q2"), Order1:=xlAscending, Key2:=.Range("C2"), Order2:=xlAscending _
        , Key3:=.Range("B2"), Order3:=xlAscending, Header:=xlYes
        .AutoFilter Field:=18, Criteria1:="=026", Operator:=xlOr, Criteria2:="=009"
        DataRng2.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A10")
        .AutoFilter
    End With
End With

End Sub

EDIT: This assumes the data is on Sheet1 and is pasting to Sheet2. Please change as required.

[ This Message was edited by: Jay Petrulis on 2002-05-03 08:37 ]

[Zac]

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).

*****