fixing error code copy filtered data from sheet to another

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
965
Office Version
  1. 2010
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



VBA 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("F2").Value
    copyRange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("B6")

End Sub


thanks advance
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,278
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
    src.AutoFilter.Range.Offset(1, 1).Resize(, 5).Copy tgt.Range("B6")
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
965
Office Version
  1. 2010
hi, Fluff no copy data anymore

sheet "data"

Book11.xlsx
ABCDEF
1datenamedate of birthaddressactivity
2101/01/2020nam107/11/1969 address1activity1
3202/01/2020nam207/08/1969address2activity2
4302/01/2020nam307/05/1987address3activity3
5403/03/2020nam420/12/1972address4activity4
6503/03/2020nam509/01/1983address5activity5
7603/03/2020nam612/10/1985address6activity6
8705/05/2020nam718/01/1955address7activity7
9806/05/2020nam8 17/10/1966 address8activity8
10907/05/2020nam919/07/1988address9activity9
111007/07/2020nam1002/09/1959address10activity10
121108/07/2020nam1101/05/1973address11activity11
131209/07/2020nam1210/10/2000address12activity12
141309/07/2020nam1311/10/2000address13activity13
Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D14Cell ValueduplicatestextNO
D13Cell ValueduplicatestextNO
D12Cell ValueduplicatestextNO
D9:D11Cell ValueduplicatestextNO
D6:D8Cell ValueduplicatestextNO
D5Cell ValueduplicatestextNO
D4Cell ValueduplicatestextNO
D3Cell ValueduplicatestextNO



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

Book11.xlsx
ABCDEF
1datenamedate of birthaddressactivity
2101/01/2020nam107/11/1969 address1activity1
3202/01/2020nam207/08/1969address2activity2
4302/01/2020nam307/05/1987address3activity3
15
Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D4Cell ValueduplicatestextNO
D3Cell ValueduplicatestextNO




Book11.xlsx
ABCDEF
1datenamedate of birthaddressactivity
15
Data




no data in sheet1



Book11.xlsx
ABCDEF
1
2COMMISSION PROJET LE :
3NOMPRENOMDATE DE NAISSANCE ADRESSEActivité
4
51
62
73
84
95
106
117
128
139
1410
1511
1612
1713
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D8Cell ValueduplicatestextNO
D7Cell ValueduplicatestextNO
D5Cell ValueduplicatestextNO
D13Cell ValueduplicatestextNO
B9:B12Cell ValueduplicatestextNO
B13Cell ValueduplicatestextNO
B13Cell ValueduplicatestextNO
B13Cell ValueduplicatestextNO
D14Cell ValueduplicatestextNO
C9:C14Cell ValueduplicatestextNO
D15:D17Cell ValueduplicatestextNO
C15:C16Cell ValueduplicatestextNO
C15:C17Cell ValueduplicatestextNO
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,278
Office Version
  1. 365
Platform
  1. Windows
You have filtered out all the data, that's why nothing is being copied.
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
965
Office Version
  1. 2010

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,278
Office Version
  1. 365
Platform
  1. Windows
No, because you have given absolutely no information to work on :eek:
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

Well-known Member
Joined
May 3, 2019
Messages
965
Office Version
  1. 2010

ADVERTISEMENT

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
ABCDEF
1datenamedate of birthaddressactivity
2101/01/2020nam107/11/1969 address1activity1
3202/01/2020nam207/08/1969address2activity2
4302/01/2020nam307/05/1987address3activity3
Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D4Cell ValueduplicatestextNO
D3Cell ValueduplicatestextNO


and become this in sheet "data"
Book11.xlsx
ABCDEF
1datenamedate of birthaddressactivity
2101/01/2020nam107/11/1969 address1activity1
Data


and become this in sheet1
Book11.xlsx
ABCDE
1
2COMMISSION PROJET LE :
3NOMPRENOMDATE DE NAISSANCE ADRESSE
4
51
62nam107/11/1969 address1activity1
73
84
95
106
117
128
139
1410
1511
1612
1713
18
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D18Cell ValueduplicatestextNO
D17Cell ValueduplicatestextNO
D16Cell ValueduplicatestextNO
D13:D15Cell ValueduplicatestextNO
D10:D12Cell ValueduplicatestextNO
D9Cell ValueduplicatestextNO
D8Cell ValueduplicatestextNO
D5Cell ValueduplicatestextNO



it remains two rows is missed about month"jan"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,278
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 3, 2019
Messages
965
Office Version
  1. 2010
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
VBA 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,278
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
filterRange.AutoFilter 2, Month(Range("b2").Value)+20, 11
 

Watch MrExcel Video

Forum statistics

Threads
1,123,279
Messages
5,600,696
Members
414,400
Latest member
Damocles2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top