Coping multiple rows of data to move to the bottom of the sheet beneath your current data

[tanyaleblanc]

Hi, I want to filter column J to move the data that has the words ACH Credit Received to the bottom of the page, beneath the other data that is on the sheet. For example, I have 18 lines of data, 8 lines are for ACH credit rec'd and I want to move these 8 lines to the bottom a few lines after the last row in my list of data. I want to move this data to row 24 (this will change as each day the number of rows changes, but ultimately I just want this data to appear at the bottom so I can total my two sets of data from column I when I'm finished separating the data.

My code is ok until I get to the Range("a2 & lrow").Select because I don't know how to move these line items to the end of the data by a couple rows.

Sheets("Bank 7").Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AD$19").AutoFilter Field:=10, Criteria1:=Array( _
        "ACH CREDIT RECEIVED", "TOTAL ACH CREDIT", "TOTAL ACH RELATED DEBITS"), Operator _
        :=xlFilterValues
        ActiveSheet.Range("$A$1:$AD$19").AutoFilter Field:=10, Criteria1:= _
        "ACH CREDIT RECEIVED"
        Range("a2 & lrow").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Row24.Activate .PasteSpecial Paste:=xlPasteValues

Once I get these data separated, I want to sum the two sets of data from column I

Range("H10").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
    Range("H24").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    Range("G24").Select
    ActiveCell.FormulaR1C1 = "IBP"
    Columns("H:H").Select
    Selection.NumberFormat = "#,##0.00"

 

[tanyaleblanc]

The delete code is deleting the whole worksheet data not just the specific data

If AutoFilterMode = True And FilterMode = True Then ActiveSheet.ShowAllData With Sheets("Bank 7") .AutoFilterMode = False lrow = .Range("A" & Rows.Count).End(xlUp).Row .Range("a1:ad" & lrow).AutoFilter 10, "ACH CREDIT RECEIVED" .AutoFilter.Range.Offset(1).EntireRow.Copy .Range("A" & lrow).Offset(3) .AutoFilter.Range.Offset(1).EntireRow.Delete .AutoFilterMode = False End With

 

[Fluff]

Shouldn't be, it should only delete the rows with "ACH CREDIT RECEIVED" in col J

 

[tanyaleblanc]

I can't seem to get this code to work, it deletes all rows in the sheet.

If AutoFilterMode = True And FilterMode = True Then ActiveSheet.ShowAllData
lRow = ActiveSheet.Range("A5000").End(xlUp).Row

lRow = ActiveSheet.Range("A5000").End(xlUp).Row
    
    Sheets("Bank 7").Select
    With ActiveSheet
    .AutoFilterMode = False
        With Range("a1:ad" & lRow)
            .AutoFilter
            .AutoFilter 10, Array("ACH CREDIT RECEIVED"), xlFilterValues
    Range("a2:ad" & lRow).Select 'this is not pasting just the ACH rows of data, its pasted all rows of data
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Sheets("Bank 7").Select
    Range("a20").Select
    ActiveSheet.Paste
    Selection.AutoFilter
    With ActiveSheet
       .AutoFilterMode = False
       End With
       Stop

 

[tanyaleblanc]

Hi, I want to filter column J to move the data that has the words ACH Credit Received to the bottom of the page, beneath the other data that is on the sheet. For example, I have 18 lines of data, 8 lines are for ACH credit rec'd and I want to move these 8 lines to the bottom a few lines after the last row in my list of data. I want to move this data to row 24 (this will change as each day the number of rows changes, but ultimately I just want this data to appear at the bottom so I can total my two sets of data from column I when I'm finished separating the data.

My code is ok until I get to the Range("a2 & lrow").Select because I don't know how to move these line items to the end of the data by a couple rows.

Sheets("Bank 7").Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AD$19").AutoFilter Field:=10, Criteria1:=Array( _
        "ACH CREDIT RECEIVED", "TOTAL ACH CREDIT", "TOTAL ACH RELATED DEBITS"), Operator _
        :=xlFilterValues
        ActiveSheet.Range("$A$1:$AD$19").AutoFilter Field:=10, Criteria1:= _
        "ACH CREDIT RECEIVED"
        Range("a2 & lrow").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Row24.Activate .PasteSpecial Paste:=xlPasteValues

Once I get these data separated, I want to sum the two sets of data from column I

Range("H10").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
    Range("H24").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    Range("G24").Select
    ActiveCell.FormulaR1C1 = "IBP"
    Columns("H:H").Select
    Selection.NumberFormat = "#,##0.00"

this code is almost working, it summed the first set of data correctly in the first column under the last amount, but the second sum didn't work, it only captured the amount in column H from row 14 to 23 but there was actually only amounts in row 11 to 20. These rows will change daily. How do I fix the second sum formula to capture the correct rows in column H and paste the total at the bottom. for eg - amounts in column H rows 11 to 20, total should paste in column H row 21