VBA Filtering Data in Loop and Copying to Other Sheet - Handling Error#

Question

I am passing the filtering criteria in a loop to filter the data in DataSheet and select the filtered data from (Col C if user select enable) or (Col D if user select disable) and copy and paste the data to other sheet.

Filtered data may be greater than 1 row, which is why i decided to copy the data by finding the last row and writing the code as: copying the visible cells only

x = Cells(Rows.Count, "A").End(xlUp).Row
Range("C2:C" & x).SpecialCells(xlCellTypeVisible).Copy
OR
x = Cells(Rows.Count, "A").End(xlUp).Row
Range("D2:D" & x).SpecialCells(xlCellTypeVisible).Copy

this code gives me an error when filtering criteria is of First row in DataSheet where instead of selecting the single row of data its selecting the entire rows of data and throwing an error while pasting it in the other sheet.

Run-time Error '1004': We can't paste because the copy area and paste area aren't same size.

Its works fine from the second filtering criteria

Possible solution i am looking for: So instead of selecting the entire column of visible data, I am looking for another loop within For..Next Loop where it loops between visible cells only and copy data the other sheet row by row.

Below is the entire Code:

Sub CommentGen_Auto()
Dim i As Long, n As Long, x As Long, lastrow As Long
Dim wb As Workbook

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set wb = ThisWorkbook
wb.Worksheets("Filter").Select
Range("H3:H100").Clear

n = Cells(Rows.Count, "B").End(xlUp).Row

For i = 3 To n
wb.Worksheets("Filter").Select
Name = Cells(i, "B").Value
groupname = Cells(i, "C").Value
Action = Cells(i, "D").Value
class = Cells(i, "E").Value

wb.Worksheets("Data").Select
Range("A1").AutoFilter Field:=1, Criteria1:=Name
Range("A1").AutoFilter Field:=2, Criteria1:=groupname
Range("A1").AutoFilter Field:=5, Criteria1:=class

If Not IsEmpty(Action) Then
If Action = "Enable" Then
x = Cells(Rows.Count, "A").End(xlUp).Row
Range("C2:C" & x).SpecialCells(xlCellTypeVisible).Copy
Else
x = Cells(Rows.Count, "A").End(xlUp).Row
Range("D2:D" & x).SpecialCells(xlCellTypeVisible).Copy
End If

wb.Worksheets("Filter").Select
lastrow = Cells(Rows.Count, "I").End(xlUp).Row + 2
Range("I" & lastrow).PasteSpecial xlPasteAll

wb.Worksheets("Data").Select
Range("A1").AutoFilter
End If

Next
wb.Worksheets("Filter").Select
Range("A1").Select
End Sub

Error Snapshot this occurs when the filter criteria in row no. 3 is the first row of data in data sheet

Expected Results, I need Sl.no to be part of the output results.

Answer 1

Please check this link

https://www.dropbox.com/s/399lvqlz2tcnpzx/Filtering\_Automation\_v1.3-Answer2.xlsm?dl=0

Answer 2

Please check this link

https://www.dropbox.com/s/399lvqlz2tcnpzx/Filtering\_Automation\_v1.3-Answer2.xlsm?dl=0

Excellent !!, Thanks Jeovany you are a Star !!..

Regards,

Hafeez

Answer 3

You are welcome Hafeez

I'm glad you found solution to your problem

The best

Regards

Jeovany