How to Copy rows from one sheet to another based on the criteria in the second sheet

[dsubash]

Dummy.xlsx
	A	B	C	D	E	F	G
1	Sl. No	Location	Date	Sales	Collected	Due	Name of Employee
2	1	Sydney	01/11/2024	500	500	0	SELF
3	2	Ireland	01/11/2024	2430	2430	0	SELF
4	3	Japan	01/11/2024	550	550	0	SELF
5	4	Tokyo	01/11/2024	350	350	0	SELF
6	5	Delhi	01/11/2024	2930	0	2930	SELF
7	6	Ohio	01/11/2024	550	550	0	SELF
8	7	New Jersey	01/11/2024	550	550	0	SELF
9	8	Bangkok	01/11/2024	2730	2730	0	SELF
10	9	Bangalore	01/11/2024	300	300	0	SELF
11	10	Sydney	01/11/2024	280	280	0	SELF
12	11	Ireland	01/11/2024	400	400	0	SELF
13	12	Japan	01/11/2024	220	220	0	SELF
14	13	Tokyo	01/11/2024	1450	1450	0	SELF
15	14	Delhi	01/11/2024	400	400	0	SELF
16	15	Ohio	01/11/2024	1840	1840	0	SELF
17	16	Sydney	01/11/2024	100	100	0	SELF
18	17	Ireland	01/11/2024	350	350	0	SELF
19	18	Sydney	01/11/2024	100	100	0	SELF
20	19	Ireland	01/11/2024	250	250	0	SELF
21	20	Japan	01/11/2024	1850	1850	0	SELF
22	21	Tokyo	01/11/2024	1900	0	0	SELF
23	22	Delhi	01/11/2024	180	180	0	OWN
24	23	Ohio	01/11/2024	100	0	100	KLM
25	24	New Jersey	01/11/2024	100	0	100	OWN
26	25	Bangkok	01/11/2024	100	0	100	OWN
27	26	Sydney	01/11/2024	100	100	0	KLM
28	27	Ireland	01/11/2024	130	0	130	KLM
29	28	Japan	01/11/2024	120	0	120	KLM
30	29	Tokyo	01/11/2024	280	280	0	KLM
31	30	Delhi	01/11/2024	400	400	0	SELF
32	31	Sydney	01/11/2024	310	310	0	KLM
33	32	Ireland	01/11/2024	380	0	380	QRS
34	33	Japan	01/11/2024	120	0	120	QRS
35	34	Tokyo	01/11/2024	40	40	0	KLM
36	35	Delhi	01/11/2024	550	550	0	SELF
37	36	Ohio	01/11/2024	2170	2170	0	SELF
38	37	New Jersey	01/11/2024	130	0	130	OWN
39	38	Bangkok	01/11/2024	130	0	130	OWN
40	39	Bangalore	01/11/2024	130	0	130	OWN
41	40	Sydney	01/11/2024	1030	0	1030	KLM
42	41	Ireland	01/11/2024	100	0	100	KLM
43	42	Japan	01/11/2024	370	0	370	KLM
44	43	Tokyo	01/11/2024	370	0	370	KLM
45	44	Delhi	01/11/2024	50	50	0	SELF
46	45	Ohio	01/11/2024	100	100	0	VWX
47	46	New Jersey	10/11/2024	100	0	100	KLM
48	47	Bangkok	10/11/2024	250	0	250	KLM
49	48	Bangalore	10/11/2024	80	0	80	KLM
50	49	Sydney	10/11/2024	100	100	0	PQR
Daily Transaction

Dummy.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	ABC Limited
2	Daily Sales Update
3
4		Date	18/11/2024		Name	SELF
5
6
7	Sl. No.	Name	Location	Date	Sales	Paid	Due
8	1	SELF	Delhi	18/11/2024	100	0	100
9	2	SELF	Delhi	18/11/2024	100	0	100
10	3	SELF	Delhi	18/11/2024	70	0	70
11	4	SELF	Delhi	18/11/2024	100	0	100
12	5	SELF	Delhi	18/11/2024	100	0	100
13	6	SELF	Delhi	18/11/2024	280	0	280
14	7	SELF	Delhi	18/11/2024	230	0	230
15	8	SELF	Delhi	18/11/2024	400	0	400
16
Report

Cells with Data Validation
Cell	Allow	Criteria
F4	List	=Names

Dear All,

I have a database sheet (Daily Transaction) which contains details like name, date, location, sales, collections, dues etc for a specific month.

I need to copy all rows from the master sheet to another sheet (Report) with Name and Date as Criteria to be entered in the report sheet.

Attached is the sample file for reference. When the name and date is changed in the report sheet, i need all rows containing specific name and date to be copied in the report sheet.

Any Formula or VB would be of great help. I am using Office Professional 2019.

Thanks in advance.
Subash D

 

[JEC]

Set up your report sheet like the pciture below(the headers and the criteriafields in K1:L2). When you did that, you can use this oneliner in VBA

Sub jec()
Sheet1.Cells(1).CurrentRegion.AdvancedFilter 2, Sheet2.Range("K1:L2"), Sheet2.Range("A1:G1")
End Sub

 

[dsubash]

Set up your report sheet like the pciture below(the headers and the criteriafields in K1:L2). When you did that, you can use this oneliner in VBA

Sub jec()
Sheet1.Cells(1).CurrentRegion.AdvancedFilter 2, Sheet2.Range("K1:L2"), Sheet2.Range("A1:G1")
End Sub

View attachment 119548

Thanks JEC,

It works as expected. I have placed an shortcut for filtering. I also need an option to clear the filter by way of an shortcut. any possibilities in including the clear filter option in your code.

 

[JEC]

Put this behind a button

sheet2.cells(1).currentregion.offset(1).clearcontents

 

[dsubash]

Thanks a Lot J.

I also want to know whether only specific columns can be reproduced in the reports page.

Actually my original sheet contains more than 50 columns, but i need to reproduce only specific columns that match the column header in the reports page. Is it possible?
I am currently hiding the columns that are unnecessary for my reports.

 

[JEC]

Type your needed headers only. Then change the location in the advanced filter line to that range. Thats about it

 

[dsubash]

Type your needed headers only. Then change the location in the advanced filter line to that range. Thats about it

Sorry J,

I am not exposed to coding.

For example, in my sample sheet, I need only the Location, Sales and collected to be reproduced in the reports page for the same Date and Name Criteria. How should i do the coding?

 

[JEC]

Ok so type these headernames in a1:c1 (exact same names as in the source sheet)

Then use the code like. Spot the difference with the first code

Sub jec()
Sheet1.Cells(1).CurrentRegion.AdvancedFilter 2, Sheet2.Range("K1:L2"), Sheet2.Range("A1:C1")
End Sub

 

[dsubash]

Thanks JEC,

It works as expected. I have placed an shortcut for filtering. I also need an option to clear the filter by way of an shortcut. any possibilities in including the clear filter option in your code.

Hi JEC,

I have been using your code successfully. Thanks.
I need the same report for a period of time, say from 01/11/2024 to 10/11/2024 or any other specific date range.
Can you suggest a code to derive at the report for a specific period. I can use Column K for From date and Column L for To Date.

 

[dsubash]

Hi JEC,

I have been using your code successfully. Thanks.
I need the same report for a period of time, say from 01/11/2024 to 10/11/2024 or any other specific date range.
Can you suggest a code to derive at the report for a specific period. I can use Column K for From date and Column L for To Date.