match sheets names with names in column and populate data

[Alaa mg]

Hello

I want matching names in column B for CUSTOMERS sheets with sheets names have already created and copy data from CUSTOMERS sheets, but without repeat , just replace new copied data when run the macro every time with old data have already copied and ignore column name for each sheet because I have sheet name.

SHEETS.xlsm
	A	B	C	D	E	F
1	DATE	NAME	ORDER NO	CONDITION	DEBIT	CREDIT
2	06/02/2023	ALAA-1	ORD-001	N/H	20,000.00
3	06/02/2023	ALAA-1	ORD-002	M/H	20,000.00	1,000.00
4	06/02/2023	ALAA-2	ORD-003	MM/N	10,000.00
5	06/02/2023	ALAA-1	ORD-004	B/N	2,000.00	100.00
6	06/02/2023	ALAA-1	ORD-004	B/NN	100.00
7	06/02/2023	ALAA-2	ORD-005	T/R	1,000.00
8	06/02/2023	ALAA-3	ORD-006	MNG	1,200.00
9	06/02/2023	ALAA-2	ORD-007	ITSR	1,200.00	150.00
10	06/02/2023	ALAA-2	ORD-007	ITSRI	200.00	100.00
11	06/02/2023	ALAA-3	ORD-008	ITRE	200.00
12	06/02/2023	ALAA-3	ORD-009	ITRE	100.00
13	06/02/2023	ALAA-3	ORD-010	ITRE		100.00
14	06/02/2023	ALAA-3	ORD-011	ITRE	1,000.00
15	06/02/2023	ALAA-3	ORD-012	ITRE	1,200.00
16	06/02/2023	ALAA-3	ORD-013	ITRE		200.00
17	06/02/2023	ALAA-3	ORD-014	ITRE	100.00	50.00
CUSTOMERS

SHEETS.xlsm
	A	B	C	D	E
1	DATE	ORDER NO	CONDITION	DEBIT	CREDIT
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ALAA-1

SHEETS.xlsm
	A	B	C	D	E
1	DATE	ORDER NO	CONDITION	DEBIT	CREDIT
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ALAA-2

expected output

SHEETS.xlsm
	A	B	C	D	E
1	DATE	ORDER NO	CONDITION	DEBIT	CREDIT
2	06/02/2023	ORD-001	N/H	20,000.00
3	06/02/2023	ORD-002	M/H	20,000.00	1,000.00
4	06/02/2023	ORD-004	B/N	2,000.00	100.00
5	06/02/2023	ORD-004	B/NN	100.00
6
7
8
9
10
11
12
13
14
15
16
17
ALAA-1

SHEETS.xlsm
	A	B	C	D	E
1	DATE	ORDER NO	CONDITION	DEBIT	CREDIT
2	06/02/2023	ORD-003	MM/N	10,000.00
3	06/02/2023	ORD-005	T/R	1,000.00
4	06/02/2023	ORD-007	ITSR	1,200.00	150.00
5	06/02/2023	ORD-007	ITSRI	200.00	100.00
6
7
8
9
10
11
12
13
14
15
16
17
ALAA-2

 

[DanteAmor]

Try this:

Sub copy_sheets()
  Dim c As Range, sh As Worksheet, ky As Variant
  
  Set sh = Sheets("CUSTOMERS")
  With CreateObject("scripting.dictionary")
    For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(xlUp))
      If c.Value <> "" Then .Item(c.Value) = Empty
    Next c
    For Each ky In .Keys
      If Evaluate("ISREF('" & ky & "'!A1)") Then
        Sheets(ky).Rows("2:" & Rows.Count).ClearContents
        sh.Range("A1").AutoFilter 2, ky
        sh.AutoFilter.Range.Offset(1).Copy Sheets(ky).Range("A2")
      End If
    Next ky
  End With
  sh.Select
  sh.ShowAllData
End Sub

 

[Alaa mg]

Hi Dante,
thanks for the code , but I think you missed this

and ignore column name for each sheet because I have sheet name.

shouldn't copy the NAME column , also there is problem about border it will delete and make without border after lastrow contain data , may you see the picture ?

SHEETS.xlsm
	A	B	C	D	E	F
1	DATE	ORDER NO	CONDITION	DEBIT	CREDIT
2	06/02/2023	ALAA-1	ORD-001	N/H	20,000.00
3	06/02/2023	ALAA-1	ORD-002	M/H	20,000.00	1,000.00
4	06/02/2023	ALAA-1	ORD-004	B/N	2,000.00	100.00
5	06/02/2023	ALAA-1	ORD-004	B/NN	100.00
6
7
8
9
10
11
12
13
14
15
16
17
ALAA-1

 

[DanteAmor]

Try:

Sub copy_sheets()
  Dim c As Range, sh As Worksheet, ky As Variant
  Dim lr As Long
  
  Application.ScreenUpdating = False
  
  Set sh = Sheets("CUSTOMERS")
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  lr = sh.Range("A" & Rows.Count).End(3).Row
  
  With CreateObject("scripting.dictionary")
    For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(xlUp))
      If c.Value <> "" Then .Item(c.Value) = Empty
    Next c
    For Each ky In .Keys
      If Evaluate("ISREF('" & ky & "'!A1)") Then
        
        Sheets(ky).Rows("2:" & Rows.Count).ClearContents
        sh.Range("A1").AutoFilter 2, ky
        sh.Range("A2:A" & lr & ",C2:F" & lr).Copy
        Sheets(ky).Range("A2").PasteSpecial xlPasteValues
        
      End If
    Next ky
  End With
  sh.Select
  sh.ShowAllData
  Application.ScreenUpdating = True
End Sub

 

[Alaa mg]

Excellent !
many thanks for this assistance.