fixing error code copy filtered data from sheet to another

[abdelfattah]

hello
i need help fixing this code from the firs time it works very well but when i run again filtered data in sheet "data" is gone and gives me runtime error 1004 no cells were found

Sub CopyFiltered()
    Dim src As Worksheet
    Dim tgt As Worksheet
    Dim filterRange As Range
    Dim copyRange As Range
    Dim lastRow As Long

    Set src = ThisWorkbook.Sheets("DATA")
    Set tgt = ThisWorkbook.Sheets("Sheet2")
    src.AutoFilterMode = False
    lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row
    Set filterRange = src.Range("A1:F" & lastRow)
    Set copyRange = src.Range("B2:F" & lastRow)
    filterRange.AutoFilter field:=2, Criteria1:="=" & Range("F2").Value
    copyRange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("B6")

End Sub

thanks advance

 

[Fluff]

How about

    src.AutoFilter.Range.Offset(1, 1).Resize(, 5).Copy tgt.Range("B6")

 

[abdelfattah]

hi, Fluff no copy data anymore

sheet "data"

Book11.xlsx
	A	B	C	D	E	F
1	N°	date	name	date of birth	address	activity
2	1	01/01/2020	nam1	07/11/1969	address1	activity1
3	2	02/01/2020	nam2	07/08/1969	address2	activity2
4	3	02/01/2020	nam3	07/05/1987	address3	activity3
5	4	03/03/2020	nam4	20/12/1972	address4	activity4
6	5	03/03/2020	nam5	09/01/1983	address5	activity5
7	6	03/03/2020	nam6	12/10/1985	address6	activity6
8	7	05/05/2020	nam7	18/01/1955	address7	activity7
9	8	06/05/2020	nam8	17/10/1966	address8	activity8
10	9	07/05/2020	nam9	19/07/1988	address9	activity9
11	10	07/07/2020	nam10	02/09/1959	address10	activity10
12	11	08/07/2020	nam11	01/05/1973	address11	activity11
13	12	09/07/2020	nam12	10/10/2000	address12	activity12
14	13	09/07/2020	nam13	11/10/2000	address13	activity13
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D14	Cell Value	duplicates	text	NO
D13	Cell Value	duplicates	text	NO
D12	Cell Value	duplicates	text	NO
D9:D11	Cell Value	duplicates	text	NO
D6:D8	Cell Value	duplicates	text	NO
D5	Cell Value	duplicates	text	NO
D4	Cell Value	duplicates	text	NO
D3	Cell Value	duplicates	text	NO

when choose from col b the month (jan,feb..etc) and i run this is what i get

Book11.xlsx
	A	B	C	D	E	F
1	N°	date	name	date of birth	address	activity
2	1	01/01/2020	nam1	07/11/1969	address1	activity1
3	2	02/01/2020	nam2	07/08/1969	address2	activity2
4	3	02/01/2020	nam3	07/05/1987	address3	activity3
15
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4	Cell Value	duplicates	text	NO
D3	Cell Value	duplicates	text	NO

Book11.xlsx
	A	B	C	D	E	F
1	N°	date	name	date of birth	address	activity
15
Data

no data in sheet1

Book11.xlsx
	A	B	C	D	E	F
1
2		COMMISSION PROJET LE :
3	N°	NOM	PRENOM	DATE DE NAISSANCE	ADRESSE	Activité
4
5	1
6	2
7	3
8	4
9	5
10	6
11	7
12	8
13	9
14	10
15	11
16	12
17	13
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D8	Cell Value	duplicates	text	NO
D7	Cell Value	duplicates	text	NO
D5	Cell Value	duplicates	text	NO
D13	Cell Value	duplicates	text	NO
B9:B12	Cell Value	duplicates	text	NO
B13	Cell Value	duplicates	text	NO
B13	Cell Value	duplicates	text	NO
B13	Cell Value	duplicates	text	NO
D14	Cell Value	duplicates	text	NO
C9:C14	Cell Value	duplicates	text	NO
D15:D17	Cell Value	duplicates	text	NO
C15:C16	Cell Value	duplicates	text	NO
C15:C17	Cell Value	duplicates	text	NO

 

[Fluff]

You have filtered out all the data, that's why nothing is being copied.

 

[abdelfattah]

could you tell me where is the line code is wrong to fix it

 

[Fluff]

No, because you have given absolutely no information to work on
If it works the first time you run it, what changes between then & the next time you run?
Is the same sheet active?
Did you change the value in F2?
Do you get any errors?

 

[abdelfattah]

sorry about my explenation is not clear i though it just adjusting some lines and you don't ask me any thing to make clear more
about active sheet is in sheet "data" and i change f2 to b2 and no any errors after adjusting your code line after this it just copy 1 row so
this is after choose " jan" from col b and filter it become this in sheet"data"

Book11.xlsx
	A	B	C	D	E	F
1	N°	date	name	date of birth	address	activity
2	1	01/01/2020	nam1	07/11/1969	address1	activity1
3	2	02/01/2020	nam2	07/08/1969	address2	activity2
4	3	02/01/2020	nam3	07/05/1987	address3	activity3
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4	Cell Value	duplicates	text	NO
D3	Cell Value	duplicates	text	NO

and become this in sheet "data"

Book11.xlsx
	A	B	C	D	E	F
1	N°	date	name	date of birth	address	activity
2	1	01/01/2020	nam1	07/11/1969	address1	activity1
Data

and become this in sheet1

Book11.xlsx
	A	B	C	D	E
1
2		COMMISSION PROJET LE :
3	N°	NOM	PRENOM	DATE DE NAISSANCE	ADRESSE
4
5	1
6	2	nam1	07/11/1969	address1	activity1
7	3
8	4
9	5
10	6
11	7
12	8
13	9
14	10
15	11
16	12
17	13
18
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D18	Cell Value	duplicates	text	NO
D17	Cell Value	duplicates	text	NO
D16	Cell Value	duplicates	text	NO
D13:D15	Cell Value	duplicates	text	NO
D10:D12	Cell Value	duplicates	text	NO
D9	Cell Value	duplicates	text	NO
D8	Cell Value	duplicates	text	NO
D5	Cell Value	duplicates	text	NO

it remains two rows is missed about month"jan"

 

[Fluff]

I'm afraid you are making no sense.
If you are filtering the sheet based on the date in B2, then you will only get one row, as there is only one row of data with that date.

 

[abdelfattah]

ok i think you right about read the date i forget this is in English i red based on Arabic despite this i write the month Jan in three rows as above picture to become like this 1/1/2020 but it gives me the one row , so i try designing the a new workbook today and in depend this code

Sub CopyFiltered()
    Dim src As Worksheet
    Dim tgt As Worksheet
    Dim filterRange As Range
    Dim copyRange As Range
    Dim lastRow As Long

    Set src = ThisWorkbook.Sheets("DATA")
    Set tgt = ThisWorkbook.Sheets("Sheet2")
    src.AutoFilterMode = False
    lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row
    Set filterRange = src.Range("A1:F" & lastRow)
    Set copyRange = src.Range("B2:F" & lastRow)
    filterRange.AutoFilter field:=2, Criteria1:="=" & Range("b2").Value
src.AutoFilter.Range.Offset(1, 2).Resize(, 5).Copy tgt.Range("B6")
End Sub

and i will inform you what happen

 

[Fluff]

Try

filterRange.AutoFilter 2, Month(Range("b2").Value)+20, 11