Forum Discussion

urbansito's avatar
urbansito
Copper Contributor
Jan 23, 2023

VBA - Need Help Please - not all rows are copied

Hi Guys,

 

Need your help, please. I have this VBA code below and it's basically a dynamic filter that copies the filtered data into a new sheet. However, the last 3 rows from my data are not copied. Do you guys know what is missing in my code? Appreciate the help!

 

Sub copy_filtered_data_CustomerAmt()

Dim count_col, count_row As Integer
Dim orig, output As Worksheet

Sheet4.Activate
count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))

list = Range(Cells(1, 1), Cells(count, 1)).Value
list = Application.Transpose(list)
list = Join(list, ",")
list = Split(list, ",")

Sheet1.Activate
ActiveSheet.Range("A5").AutoFilter field:=1, Criteria1:=list, Operator:=xlFilterValues

ThisWorkbook.Sheets("CustomerAmt").Cells.clearcontents
Worksheets("Data").Activate

Set orig = ThisWorkbook.Sheets("Data")
Set output = ThisWorkbook.Sheets("CustomerAmt")

count_row = WorksheetFunction.CountA(Range("A5", Range("A5").End(xlDown)))
count_col = WorksheetFunction.CountA(Range("A5", Range("A5").End(xlToRight)))

ActiveSheet.Range("A5").AutoFilter field:=7, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic

orig.Range(Cells(5, 1), Cells(count_row, count_col)).SpecialCells(xlCellTypeVisible).Copy
output.Cells(9, 1).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

End Sub

excel
Formulas and Functions
Macros and VBA

6 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jan 23, 2023
    Is your data contiguous? Where does a control+arrow-down take you, starting from cell A1?
    • urbansito's avatar
      urbansito
      Copper Contributor
      Jan 23, 2023
      my Cell A1 to A4 is blank data since I'm having my data starts at A5
      • mtarler's avatar
        mtarler
        Silver Contributor
        Jan 23, 2023
        BTW, it appears that everything before:
        ThisWorkbook.Sheets("CustomerAmt").Cells.clearcontents
        is acting on "Sheet4" and has no affect on the copy/paste
        and then after that you have:
        ActiveSheet.Range("A5").AutoFilter field:=7, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic
        orig.Range(Cells(5, 1), Cells(count_row, count_col)).SpecialCells(xlCellTypeVisible).Copy
        which will filter for LAST month and then only copy visible so is it that the last 3 rows are THIS month?
    • urbansito's avatar
      urbansito
      Copper Contributor
      Jan 23, 2023
      yes, Jan my data is contagious and when a do ctrl+arrow -down, it did bring me to the last row. the problem is the last three rows are not copied
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jan 23, 2023

    I suspect that maybe you have blanks in column A. The macro is using the ctrl down function to "find" the end of the data but that will find the end of 'continuous' data. ALSO you are using COUNTA function so even if it goes to the end and there are any blanks in the column that count will not count them and hence your column reference will be short that many.  Maybe use something like this instead:

    count = Activesheet.cells(activesheet.usedrange.rows.count+1,1).End(xlUp).row
    I also see you ".activate" in multiple cases, which I recommend against. Just use the reference to that sheet directly or assign that sheet to a variable and use that variable. You already do that in some cases.
    Also, maybe you could explain to me what you are doing with these lines:
    list = Application.Transpose(list)
    list = Join(list, ",")
    list = Split(list, ",")
    I ask to either a) learn something new or b) recommend a change to make it more efficient