1

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 to 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

DataSheet to Filter: DataSheet to Filter

Error Snapshot: Error Snapshot

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Iam_hafeez
  • 113
  • 10
  • This is quite a long task to follow. Could you boil down your main problem to a few rows of code and post new questions for side problems, please? – Wolfgang Jacques May 11 '19 at 09:03
  • Hey @WolfgangJacques, you can check the images for the problem defination, I am getting an error from the looping code. when i filter the data sheet using the variables, this gives me an error when the filter criteria is first row of data sheet. – Iam_hafeez May 11 '19 at 10:16
  • 1
    Welcome to the site! I agree with @WolfgangJacques - I think "No Comments Found" is one question, and Error 1004 is another question. You can edit this question with the "edit" link to cut the second half, and then paste it into another question. You can have more than one question open at a time, and site custom encourages smaller, more focused questions. You can also link questions together it you want to, just by putting the URL of one in the other. – cxw May 11 '19 at 10:42
  • 1
    Sure @cxw I will create separate questions, thanks. – Iam_hafeez May 11 '19 at 10:56
  • 1
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ May 13 '19 at 06:09

0 Answers0