VBA Filter Names from List One by One and then Copy filtered data

[Sufiyan97]

I have a data like below

I a VBA to Filter Names from List (J16:J20) One by One and then Copy the filtered data:

Book1
	C	D	E	F	G	H	I	J	K	L
1		Name	Amount
2		A	5
3		A	5
4		B	10
5		B	10
6		C	15
7		C	15
8		D	20
9		D	20
10		E	25
11		E	25
12
13		Total Quantity	150
14		Total Amount	4500
15
16								A
17								B
18								C
19								D
20								E
21
22
23
24
25
Sheet1

Cell Formulas
Range	Formula
E13	E13	=SUBTOTAL(9,E2:E11)
E14	E14	=E13*30

 

[Sufiyan97]

Copying as image would be even simpler; for example:

Hey Anthony,

That is really amazing.

Didn't know that it would be simpler for you.

That is the thing which I was doing manually filtering and copying the data and sending to my brother for his monthly bills.

This is the thing which I wanted to do automatically.

A BIG THANK YOU for all your help.

 

[Anthony47]

It took a while, but we got it...
Thank you for the feedback

 

[Sufiyan97]

Hi Anthony

What if I want to change the criteria column for example, currently it is filtering in names column in column D
I want to change the name column to E if the list of names is in column E.
Which line should I modify?

 

[Anthony47]

Didn't understand what you wish to modify, but:
This set to D1 the start position for the filtered column

Set fRange = Range(Range("D1"), Range("D1").End(xlDown).Offset(0, 1))

This set the start position for the names to Row 16 / Column J

For I = 16 To 1000
    cFilt = Cells(I, "J").Value

("J" is also recalled in the Messagebox)

This extend the selection to the column at the left of the filtered column:

Range(fRange.Cells(1, 0), etc etc

If this isn't sufficient for your situation then please describe again the request

 

[Sufiyan97]

Hi Anthony
Thanks again

I modified below lines and this is working:

Set fRange = Range(Range("E1"), Range("E1").End(xlDown).Offset(1, 0))   'Assuming the table starts in D1

Range(fRange.Cells(1, -1), fRange.Cells(fRC + 3, fRange.Columns.Count)).Copy

So the question was like this if I interchange the Amount and name columns

Book2
	C	D	E
1	Date	Amount	Name
2	9/28/2022	5	A
3	9/28/2022	5	A
4	1/1/1900	10	B
5	9/29/2022	10	B
6	9/30/2022	15	C
7	9/30/2022	15	C
8	10/1/2022	20	D
9	10/1/2022	20	D
10	1/1/1900	25	E
11	10/2/2022	25	E
12
13		150	Total Quantity
14		4500	Total Amount
Sheet1

Cell Formulas
Range	Formula
C4,C10	C4	=B3+1
D13	D13	=SUBTOTAL(9,D2:D11)
D14	D14	=D13*30

 

[Anthony47]

Glad you was able to adapt the code to your data structure