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]

I was able to update the code to the below, but I'm still having a small problem. The range - Range("a2:ad" & lRow).Select is posting all rows of data even the items that are not ACH Credit Received, I think it has to do with the fact that I chose a2:ad even though the ACH data row first appears on a9, but I don't want to enter a9 because this data will change in the rows day over day.

I just want to be able to put a filter on column 10 that has ACH credit Received and cut those lines and paste further down the page say on row 20

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

 

[Fluff]

How about

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

 

[tanyaleblanc]

I'm getting this error message at this part in the code - .AutoFilter.Range.Offset(1).Copy .Range("A" & Rows.Count).End(xlUp).Offset(2)

I don't want to copy it, I want to cut it out of where it is and paste further down on row 20

How about

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

 

[Fluff]

Do you have any merged cells?

 

[tanyaleblanc]

no merged cells

Do you have any merged cells?

 

[tanyaleblanc]

I changed your copy to cut and it's working but it's taking all data and cutting it to cell a20 instead of just the filtered data for ACH credit rec'd
the delete code deletes the whole sheet

Do you have any merged cells?

 

[Fluff]

Do you want those rows deleted after moving them to the bottom of the sheet, or just left blank?

 

[tanyaleblanc]

I want those rows deleted so no blank lines once I unfilter

Do you want those rows deleted after moving them to the bottom of the sheet, or just left blank?

 

[Fluff]

In that case try

   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