extract data from sheet to another based on specific date

[abdo meghari]

Hello
I want extracting data from BRAND sheet to BALANCE sheet based on date=09/02/2023(dd/mm/yyyy) in column A
but should not repeat extracting to the bottom for BALANCE sheet , should clear data for BALANCE sheet before brings data from BRAND sheet.

MM with corrected CBs v03 a (2).xlsm
	A	B	C	D	E	F	G	H
1	DATE	CODE	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
2	09/02/2023	BSJG580120020	BS 1200R20	G580	JAP	200.00	2,200.00	440,000.00
3	09/02/2023	BSTG580120020	BS 1200R20	G580	THI	250.00	2,000.00	500,000.00
4	09/02/2023	BSJR187120020	BS 1200R20	R187	JAP	300.00	2,100.00	630,000.00
5	09/02/2023	BSTR187120020	BS 1200R20	R187	THI	350.00	2,200.00	770,000.00
6	09/02/2023	BSJG580120024	BS 1200R24	G580	JAP	400.00	3,000.00	1,200,000.00
7	10/02/2023	BSTG582120024	BS 1200R24	G582	THI	450.00	2,800.00	1,260,000.00
8	11/02/2023	BSJR23070016	BS 700R16	R230	JAP	500.00	800.00	400,000.00
9	12/02/2023	BSJR23075016	BS 750R16	R230	JAP	550.00	900.00	495,000.00
10	13/02/2023	BSJVSJ75016	BS 750R16	VSJ	JAP	600.00	1,300.00	780,000.00
11	14/02/2023	BSJVSJ140020	BS 1400R20	VSJ	JAP	650.00	2,000.00	1,300,000.00
12	15/02/2023	BSJR180140020	BS 1400R20	R180	JAP	700.00	3,500.00	2,450,000.00
BRANDS

before

MM with corrected CBs v03 a (2).xlsm
	D	E	F	G	H	I	J	K
1	DATE	CODE	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
BALANCE

after
[

MM with corrected CBs v03 a (2).xlsm
	D	E	F	G	H	I	J	K
1	DATE	CODE	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
2	09/02/2023	BSJG580120020	BS 1200R20	G580	JAP	200.00	2,200.00	440,000.00
3	09/02/2023	BSTG580120020	BS 1200R20	G580	THI	250.00	2,000.00	500,000.00
4	09/02/2023	BSJR187120020	BS 1200R20	R187	JAP	300.00	2,100.00	630,000.00
5	09/02/2023	BSTR187120020	BS 1200R20	R187	THI	350.00	2,200.00	770,000.00
6	09/02/2023	BSJG580120024	BS 1200R24	G580	JAP	400.00	3,000.00	1,200,000.00
BALANCE

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Sheets("BALANCE").UsedRange.Offset(1).ClearContents
    With Sheets("BRANDS")
        .Range("A1").CurrentRegion.AutoFilter 1, "=09-02-2023"
        .AutoFilter.Range.Offset(1).Copy Sheets("BALANCE").Range("A2")
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[abdo meghari]

thanks, but it doesn't copy anything .
by the way should past in D2 for BALANCE sheet , not A2

 

[mumps]

I tested the macro using the data you posted and it worked properly. You just have to change the the A2 to D2. Could you upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[abdo meghari]

You just have to change the the A2 to D2

I really did it .
here is the file
TEST.xlsm

 

[mumps]

The date format in your file was different from the data your posted. Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Sheets("BALANCE").UsedRange.Offset(1).ClearContents
    With Sheets("BRANDS")
        .Range("A1").CurrentRegion.AutoFilter 1, "=2023-02-09"
        .AutoFilter.Range.Offset(1).Copy Sheets("BALANCE").Range("A2")
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[abdo meghari]

thanks again !
for the uploaded file doesn't work and I note deleting borders for row2!

 

[alansidman]

Here is an alternative solution using Power Query and a Parameter

Load your table to the PQ Editor

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"CODE", type text}, {"BRAND", type text}, {"TYPE", type text}, {"ORIGIN", type text}, {"QTY", Int64.Type}, {"PRICE", Int64.Type}, {"TOTAL", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([DATE] = Table3))
in
    #"Filtered Rows"

Create a second table in Excel as shown in the attached for dates and load it to the PQ Editor

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Date = #"Changed Type"{0}[Date]
in
    Date

When ever you change the date in the second date table and click on refresh it will update your output table.

Book1
	A	B	C	D	E	F	G	H	I	J
1	DATE	CODE	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL		Date
2	2/9/2023	BSJG580120020	BS 1200R20	G580	JAP	200	2200	440000		2/9/2023
3	2/9/2023	BSTG580120020	BS 1200R20	G580	THI	250	2000	500000
4	2/9/2023	BSJR187120020	BS 1200R20	R187	JAP	300	2100	630000
5	2/9/2023	BSTR187120020	BS 1200R20	R187	THI	350	2200	770000
6	2/9/2023	BSJG580120024	BS 1200R24	G580	JAP	400	3000	1200000
7	########	BSTG582120024	BS 1200R24	G582	THI	450	2800	1260000
8	########	BSJR23070016	BS 700R16	R230	JAP	500	800	400000
9	########	BSJR23075016	BS 750R16	R230	JAP	550	900	495000
10	########	BSJVSJ75016	BS 750R16	VSJ	JAP	600	1300	780000
11	########	BSJVSJ140020	BS 1400R20	VSJ	JAP	650	2000	1300000
12	########	BSJR180140020	BS 1400R20	R180	JAP	700	3500	2450000
13
14
15
16	DATE	CODE	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
17	2/9/2023	BSJG580120020	BS 1200R20	G580	JAP	200	2200	440000
18	2/9/2023	BSTG580120020	BS 1200R20	G580	THI	250	2000	500000
19	2/9/2023	BSJR187120020	BS 1200R20	R187	JAP	300	2100	630000
20	2/9/2023	BSTR187120020	BS 1200R20	R187	THI	350	2200	770000
21	2/9/2023	BSJG580120024	BS 1200R24	G580	JAP	400	3000	1200000
Sheet1

 

[abdo meghari]

thanks Alan , my project depends on vba , so I can't using PQ , sorry !

 

[alansidman]

No worries. Thought I would offer up an alternative. Good Luck.